问题描述:

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

网友答案:

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
网友答案:

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;
/
相关阅读:
Top