SQL> select LENGTH(translate('123456','A1234567890','A')) from dual;LENGTH(TRANSLATE('123456','A1234567890','A')) ---------------------------------------------全数字SQL> select LENGTH(translate('123456AAA','A1234567890','A')) from dual;LENGTH(TRANSLATE('123456AAA','A1234567890','A'))------------------------------------------------ 3非全数字
字符串处理自然要用正则表达式了,不过要你的oracle至少10g,这是我查询的organization_no中含有字母的语句,你可以参考一下select * from t_agent_info where case when regexp_like(organization_no,'[[:alpha:]]') then 1 else 0 end = 1;
select * from t_agent_info where regexp_like(organization_no,'[^\d]+')
with t as (select '1234567890' a from dual union all select '111111111111111' a from dual union all select '111111222222' a from dual union all select '111abc111d1124' a from dual union all select 'abc$$@#@' a from dual) select * from t where not regexp_like(t.a, '+[^[:digit:]$]+')
create or replace function Isnumber(P_column in varchar2) return number is l_t number; begin l_t := to_number(P_column); return 1; exception when others then return 0; end;
---------------------------------------------全数字SQL> select LENGTH(translate('123456AAA','A1234567890','A')) from dual;LENGTH(TRANSLATE('123456AAA','A1234567890','A'))------------------------------------------------
3非全数字
(select '1234567890' a
from dual
union all
select '111111111111111' a
from dual
union all
select '111111222222' a
from dual
union all
select '111abc111d1124' a
from dual
union all
select 'abc$$@#@' a from dual)
select * from t where not regexp_like(t.a, '+[^[:digit:]$]+')
l_t number;
begin
l_t := to_number(P_column);
return 1;
exception
when others then
return 0;
end;