SQL> select 1 from dual where translate('121432X', '!1234567890X', '!') is null; 1 ---------- 1用一个最不可能出现的符号替换 '!'
create or replace function yourtest(str1 varchar2) return varchar2 as str2 varchar2; str3 varchar2; begin str2:=ltrim(str1,'1234567890X'); if str2 is Null or str2='' then str3:='right' else str3:='wrong' end if; return str3; end; /select yourtest('yourstring') from dual;
create or replace function uf_num(av_strin in varchar2) return varchar2 is v_len number; i number; v_str_new varchar2(255); v_chr char(2);begin v_str_new:=translate(rtrim(ltrim(av_strin)),'1234567890x','00000000000'); v_len:=length(av_strin); i:=1; while i<=v_len loop v_chr:=substr(v_str_new,i,1); if v_chr<>'0' then return 'F'; end if; i:=i+1; end loop; return 'T'; end;SQL> select uf_num('12345') from dual;UF_NUM('12345') -------------------------------------------------------------------------------- TSQL> select uf_num('12345SDFSD') from dual;UF_NUM('12345SDFSD') -------------------------------------------------------------------------------- F
一条语句最简单 SQL> select ltrim('123X','1234567890X') from dual;LTRIM('123X','1234567890X') ---------------------------SQL> select ltrim('123Xa','1234567890X') from dual;LTRIM('123XA','1234567890X') ---------------------------- a
----------
1用一个最不可能出现的符号替换 '!'
return varchar2 as
str2 varchar2;
str3 varchar2;
begin
str2:=ltrim(str1,'1234567890X');
if str2 is Null or str2='' then
str3:='right'
else
str3:='wrong'
end if;
return str3;
end;
/select yourtest('yourstring') from dual;
v_len number;
i number;
v_str_new varchar2(255);
v_chr char(2);begin
v_str_new:=translate(rtrim(ltrim(av_strin)),'1234567890x','00000000000');
v_len:=length(av_strin);
i:=1;
while i<=v_len loop
v_chr:=substr(v_str_new,i,1);
if v_chr<>'0' then
return 'F';
end if;
i:=i+1;
end loop;
return 'T';
end;SQL> select uf_num('12345') from dual;UF_NUM('12345')
--------------------------------------------------------------------------------
TSQL> select uf_num('12345SDFSD') from dual;UF_NUM('12345SDFSD')
--------------------------------------------------------------------------------
F
SQL> select ltrim('123X','1234567890X') from dual;LTRIM('123X','1234567890X')
---------------------------SQL> select ltrim('123Xa','1234567890X') from dual;LTRIM('123XA','1234567890X')
----------------------------
a