Check string for numbers and characters in Oracle 11G

Question

I am working on something. I got to check a string for validation. This string has a country code and a university code (from their own tables in the database) and a bunch of numbers. Now I need to validate this string. I need to check this string on the country code and university code and if it has numbers yes or no.

I have tried alot of things at the moment. I tried to use alot of if statements, regexp_like, instr, substr, select statements and others. But I am not capable of checking the string for it's country and university code (and if it has matching numbers).

My code at the moment is as follows:

create or replace function checkForCorrectness (
  isin varchar2)
return integer
as
  isCorrect integer := 0;
  checkISIN varchar2(50);
  checkCountryCode country.code%type;
  checkUniversityCode university.code%type;

  e_onbekendeLandCode exception;
  e_onbekendeUniCode exception;
  e_lengteNummer exception;
begin
 --checkISIN := isin;
 /*
 if checkISIN like '%NL%'
 then dbms_output.put_line('Beschikt over een landcode');
  if checkISIN like '%KTU%'
   then dbms_output.put_line('Beschikt over een universiteit code');
    if checkISIN like ''
     then dbms_output.put_line('Beschikt over een nummerreeks');
   end if;
  end if;
 end if;
 */
 --select isin
 --into checkISIN
 --from dual
 --where regexp_like(checkISIN, '^[[:digit:]]+$');
 isin := regexp_like(isin, '[[:digit:]]');
 dbms_output.put_line(checkISIN);

  return isCorrect;

exception
 when e_lengteNummer
 then dbms_output.put_line('Foutmelding: Nummereeks is kleiner dan 9');

 when e_onbekendeLandCode
 then dbms_output.put_line('Foutmelding: Landcode is niet geldig of bestaat niet');

 when e_onbekendeUniCode
 then dbms_output.put_line('Foutmelding: Universiteit code is niet geldig of bestaat niet');

end checkForCorrectness;
/
show errors function checkForCorrectness
/*
begin
 dbms_output.put_line(checkForCorrectness('NL 4633 4809 KTU'));
end;

At the moment I am so confused I don't even know what to do anymore. I hope some of you guys could help me out.

To make a long story short. I got this check value (and many more):

ASSERT_EQUALS(checkForCorrectness('NL 4633 4809 KTU'),1);
ASSERT_EQUALS(checkForCorrectness('NL 4954 2537 7808 MSM'),1);

The input is a string that contains a country code (NL) a number (4633 4809) and a university code (KTU). if the result is true or it is validated it returns 1 else 0.

I hope my question is not too vague.

If someone can explain to me what kind of functions and steps I need to make/take, would be awesome

Thanks in advance

Are all country codes two characters? Are all university codes three characters? Are any characters other than spaces and numbers between the country code and university code allowed? Are there any restrictions on how those are assembled (i.e. would NL 4 9 5 425 MSM be valid)? Or do numbers always appear in groups of 4? Are you trying to validate the country and university code against lookup tables? Or just check that they are two and three letter codes respectively? — Justin Cave
– Justin Cave, Commented May 18, 2015 at 20:00
Country and university codes could be longer then 2 or 3 characters. for the numbers is pairs of 4 necessary(untill not possible). I don't know what is the best way to check the country and university code, but I would like to check those two from their own tables (if that is possible) — Maxime de Lange
– Maxime de Lange, Commented May 18, 2015 at 20:14
OK. Then what, exactly, is the rule you want to enforce? 1 or more letters followed by a space followed by 1 or more sets of four consecutive digits followed by a space followed by 1 or more letters? Can the country code always be parsed out by reading up to the first space and can the university code be parsed out by reading from the last space? — Justin Cave
– Justin Cave, Commented May 18, 2015 at 20:22
As far as my test sheet goes the country code could variate in length, but is always at front. The university code could also variate in length, but is always at the end of the code. In the middle the numbers could variate from length but they need to be paired by 4 as much as possible. between the 3 values there is always one space. I need to put the ISIN string from the test sheet into the function. From there I need to check if the country and university code exist from within the tables and as last I need to check if there are a minimum of 8 digits. sorry for bad english — Maxime de Lange
– Maxime de Lange, Commented May 18, 2015 at 20:30
What does "paired by 4 as much as possible" mean? Are there cases where the numbers don't appear in pairs of 4? What are the rules around that? Can the country code always be parsed out by reading up to the first space and can the university code be parsed out by reading from the last space? They always appear at the beginning and the end but do they always run to the first space and from the last space? — Justin Cave
– Justin Cave, Commented May 18, 2015 at 20:55

Bryan Dellinger · Accepted Answer · 2015-05-18 22:17:53Z

1

ok I'm not exactly clear on what you are doing however maybe the following might help. creating a table from data

create table mytable as select 'NL 4633 4809 KTU' txt from dual;

you can use regex to split the words up into columns

SELECT  REGEXP_SUBSTR (txt, '[^ ]+', 1, 1)    AS part_1
,       REGEXP_SUBSTR (txt, '[^ ]+', 1, 2)    AS part_2
,       REGEXP_SUBSTR (txt, '[^ ]+', 1, 3)    AS part_3
,       REGEXP_SUBSTR (txt, '[^ ]+', 1, 4)    AS part_4
FROM    mytable;

this would give you 4 columns with your words split out.

part1 part2 part3 part4
NL 4633 4809 KTU

or you could use regexp with a connect by statement to turn your words into rows.

select REGEXP_SUBSTR (txt, '[^ ]+', 1, level) val from mytable connect by level <= regexp_count(txt,' ') + 1;

this would give you four rows

NL
4633
4809
KTU

answered May 18, 2015 at 22:17

Bryan Dellinger

5,43210 gold badges40 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maxime de Lange Over a year ago

Thanks for the explanation. With a little bit tweeking it worked. Thanks again. And I mark your awnser as the right given one. Cheers

Chirag Mehta · Accepted Answer · 2015-05-19 07:37:37Z

0

Might below sql block code will help to get what you want as output....

set serveroutput on
Declare
  vStr Varchar2(100);
  iB Number := 0;
Begin
  vStr := 'NL 4633 4809 KTU';

  select distinct 1
  into iB
  from country where name = Substr(vStr,1,2);

  if iB = 1 then
    dbms_output.put_line('country is legit');
  end if;

  select distinct 1
  into iB
  from university where name = Substr(vStr,length(vStr)-2,3);

  if iB = 1 then
    dbms_output.put_line('uni. is legit');
  end if; 

End;
/

answered May 19, 2015 at 7:37

Chirag Mehta

91 silver badge2 bronze badges

Collectives™ on Stack Overflow

Check string for numbers and characters in Oracle 11G

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related