如何判断一个5字符串中只能是数字或‘X’？

[gemini419]

如题！

[zzwind5]

SQL> select 1 from dual where translate('121432X', '!1234567890X', '!') is null;

1
----------
1

用一个最不可能出现的符号替换 '!'

[gemini419]

还有没有其他的方法啊？

[tmdxht]

写个函数！

[hfyang9095]

学习，

[gemini419]

怎么写？

[超叔csdn]

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;

[tmdxht]

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')
--------------------------------------------------------------------------------
T

SQL> select uf_num('12345SDFSD') from dual;

UF_NUM('12345SDFSD')
--------------------------------------------------------------------------------
F

[xjqqxjqq]

一条语句最简单
SQL> select ltrim('123X','1234567890X') from dual;

LTRIM('123X','1234567890X')
---------------------------

SQL> select ltrim('123Xa','1234567890X') from dual;

LTRIM('123XA','1234567890X')
----------------------------
a