在Oracle 11G中检查字符串中的数字和字符 [英] Check string for numbers and characters in Oracle 11G
问题描述
我正在做某事.我必须检查一个字符串以进行验证.该字符串具有国家代码和大学代码(来自数据库中自己的表)和一堆数字.现在,我需要验证此字符串.我需要检查国家(地区)代码和大学代码上的字符串,以及是否包含数字(是或否).
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.
此刻我已经尝试了很多东西.我尝试使用很多if语句,regexp_like,instr,substr,select语句和其他语句.但是我无法检查该字符串的国家和大学代码(以及是否具有匹配的数字).
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).
此刻我的代码如下:
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);
输入是一个字符串,其中包含国家/地区代码(NL),数字(4633 4809)和大学代码(KTU).如果结果为true或经过验证,则返回1,否则返回0.
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
预先感谢
推荐答案
好,我不清楚您在做什么,但是以下内容可能会有所帮助.从数据创建表
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;
这将为您提供4列,单词分开显示.
this would give you 4 columns with your words split out.
part1 part2 part3 part4
NL 4633 4809 KTU
或者您可以将regexp与connect by语句一起使用,以将单词变成行.
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;
这将给您四行
NL
4633
4809
KTU
这篇关于在Oracle 11G中检查字符串中的数字和字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!