select * from table where len(ltrim(column,'012345678'))<>len(column)
意思应该差不多,写法上要测一下,现在没有oracle的环境,你可以测一下
zmgowin(隐者(龙祖宗)) 老大,我的字符串不在表中。 还有,我不是要找出表的记录。
试下用instr()结合你自己写的function吧。
create or replace function f_firstNumber(str varchar2) return number is f_tmp varchar2(1); f_pos integer:=1;begin while f_pos <= length(str) loop f_tmp := substr(str,f_pos,1); if (f_tmp between '0' and '9') then goto f_posation; end if; f_pos := f_pos + 1; end loop; <<f_posation>> if (f_pos <= length(str)) then return f_pos; end if; return 0; -- not found end f_firstNumber;
共有几个数字,使所有的数字还是连续的几个数字? 这个函数是统计所有的数字,如果你要连续的数字就修改一下吧。 create or replace function f_countNumber(str varchar2) return number is f_tmp varchar2(1); f_pos integer:=1; f_count integer:=0;begin while f_pos <= length(str) loop f_tmp := substr(str,f_pos,1); if (f_tmp between '0' and '9') then f_count := f_count + 1; end if; f_pos := f_pos + 1; end loop; return f_count; end f_countNumber;
--得到字符串中第一个数字出现的位置,如果没有数字,则返回0,注意maxPos的最大值可以自己修改 create or replace function f_getFirstNumberPos(str varchar2) return integer is pos integer := 0; maxPos integer := 99999; begin select least( decode(instr(str,'0'),0,maxPos,instr(str,'0')) ,decode(instr(str,'1'),0,maxPos,instr(str,'1')) ,decode(instr(str,'2'),0,maxPos,instr(str,'2')) ,decode(instr(str,'3'),0,maxPos,instr(str,'3')) ,decode(instr(str,'4'),0,maxPos,instr(str,'4')) ,decode(instr(str,'5'),0,maxPos,instr(str,'5')) ,decode(instr(str,'6'),0,maxPos,instr(str,'6')) ,decode(instr(str,'7'),0,maxPos,instr(str,'7')) ,decode(instr(str,'8'),0,maxPos,instr(str,'8')) ,decode(instr(str,'9'),0,maxPos,instr(str,'9')) ) into pos from dual; select decode(pos,maxPos,0,pos) into pos from dual; return pos; end f_getFirstNumberPos; --调用如下: select f_getFirstNumberPos('abfvc'),f_getFirstNumberPos('ffd77') from dual;
--返回字符串中的数字的个数 create or replace function f_getNumberCount(str varchar2) return integer is numberCount integer := 0; begin select nvl( length(str) - length( replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') ),0) into numberCount from dual; return numberCount; end f_getNumberCount; --调用如下 select f_getNumberCount('ffg'),f_getNumberCount('123456790454451245454511212g') from dual
老大,我的字符串不在表中。
还有,我不是要找出表的记录。
return number
is
f_tmp varchar2(1);
f_pos integer:=1;begin while f_pos <= length(str) loop
f_tmp := substr(str,f_pos,1);
if (f_tmp between '0' and '9') then
goto f_posation;
end if;
f_pos := f_pos + 1;
end loop;
<<f_posation>>
if (f_pos <= length(str)) then
return f_pos;
end if; return 0; -- not found
end f_firstNumber;
这个函数是统计所有的数字,如果你要连续的数字就修改一下吧。
create or replace function f_countNumber(str varchar2)
return number
is
f_tmp varchar2(1);
f_pos integer:=1;
f_count integer:=0;begin while f_pos <= length(str) loop
f_tmp := substr(str,f_pos,1);
if (f_tmp between '0' and '9') then
f_count := f_count + 1;
end if;
f_pos := f_pos + 1;
end loop;
return f_count;
end f_countNumber;
create or replace function f_getFirstNumberPos(str varchar2)
return integer
is
pos integer := 0;
maxPos integer := 99999;
begin
select least(
decode(instr(str,'0'),0,maxPos,instr(str,'0'))
,decode(instr(str,'1'),0,maxPos,instr(str,'1'))
,decode(instr(str,'2'),0,maxPos,instr(str,'2'))
,decode(instr(str,'3'),0,maxPos,instr(str,'3'))
,decode(instr(str,'4'),0,maxPos,instr(str,'4'))
,decode(instr(str,'5'),0,maxPos,instr(str,'5'))
,decode(instr(str,'6'),0,maxPos,instr(str,'6'))
,decode(instr(str,'7'),0,maxPos,instr(str,'7'))
,decode(instr(str,'8'),0,maxPos,instr(str,'8'))
,decode(instr(str,'9'),0,maxPos,instr(str,'9'))
) into pos from dual;
select decode(pos,maxPos,0,pos) into pos from dual;
return pos;
end f_getFirstNumberPos;
--调用如下:
select f_getFirstNumberPos('abfvc'),f_getFirstNumberPos('ffd77') from dual;
create or replace function f_getNumberCount(str varchar2)
return integer
is
numberCount integer := 0;
begin
select
nvl(
length(str) -
length(
replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')
),0)
into numberCount
from dual;
return numberCount;
end f_getNumberCount;
--调用如下
select f_getNumberCount('ffg'),f_getNumberCount('123456790454451245454511212g') from dual