create table exTable(
exCol VARCHAR2(50)
);
insert into exTable (exCol) values ('JS123456789DS');
insert into exTable (exCol) values ('JS003456789DS');
insert into exTable (exCol) values ('005680056');
insert into exTable (exCol) values ('JS123DS');select case
when exCol字段中有9个数字 then
'正确'
when a = 2 then
'错误'
end exCol
from exTable t;求问:exCol字段中有9个数字 此处应该怎么写
when EXCOL in( '1','3','4') then
'正确'
when EXCOL = '2' then
'错误'
end exCol
from exTable
--使用正则表达式:
--如果重复的数字也算上的话:
SQL> select * from extable;
EXCOL
--------------------------------------------------
JS123456789DS
JS003456789DS
005680056
JS123DSSQL> select *
2 from extable
3 where length(regexp_substr(excol,'[0-9]+'))=9
4 /
EXCOL
--------------------------------------------------
JS123456789DS
JS003456789DS
005680056
SQL>
SQL> select * from extable
2 where length(regexp_replace(excol,'[[:alpha:]]',''))=9
3 /
EXCOL
--------------------------------------------------
JS123456789DS
JS003456789DS
005680056
SQL>
when length(excol) - nvl(length(translate(excol, '\0123456789', '\')),0) = 9 then
'正确'
when a = 2 then
'错误'
end exCol
from exTable t;