-- select * from tabname where trim(replace(col_name, '0123456789 ', ' ')) is null
我就是在oracle中。translate不知道该怎么用?
select nvl(trim(translate('123123345','0123456789',' ')),0) from dual
is_number 这是别人写的函数,你的库里没有这个函数,肯定报错了 。
用函数REGEXP_LIKE : select * from tab where REGEXP_LIKE(c,'[:digit:]');
create or replace function is_number(i_str varchar2) return number is v_number number; begin select to_number(i_str) into v_number from dual; if v_number is not null then return 1; else return 0; end if; EXCEPTION WHEN OTHERS THEN return 0; end; / select is_number('123') from dual; select is_number('123dd') from dual; select is_number('123.44') from dual;
给你个例子 CREATE OR REPLACE FUNCTION IS_NUMBERIC(MyStr VARCHAR2) RETURN varchar2 IS ISNUM NUMBER; BEGIN ISNUM:=TO_NUMBER(MyStr); RETURN ISNUM||' is number'; EXCEPTION WHEN INVALID_NUMBER THEN RETURN MyStr|| 'is not number'; WHEN OTHERS THEN RETURN MyStr||' is not number'; END;接下来执行 select IS_NUMBERIC('9') from dual; 看看
select col_name from t where trim(translate(col_name,'0123456789.',' ')) is null;
column t_num for a20;drop table t; create table t(col_name varchar2(20)); insert into t(col_name) values('123.44'); insert into t(col_name) values('dd123.44'); insert into t(col_name) values('12bb3.44'); insert into t(col_name) values('884.54'); insert into t(col_name) values('999.'); commit;select col_name from t where trim(translate(col_name,'0123456789.',' ')) is null and length(col_name)-length(replace(col_name,'.',''))<=1 -- 只允许有一个小数点(.)字符 and substr(col_name,length(col_name),1)<>'.'; -- 小数点不能在最后一位
-- 方法一:直接用SQL语句 select col_name from t where trim(translate(col_name,'0123456789.',' ')) is null and length(col_name)-length(replace(col_name,'.',''))<=1; -- 只允许有一个小数点(.)字符-- 方法二:用函数 create or replace function is_number(i_str varchar2) return number is v_number number; begin select to_number(i_str) into v_number from dual; if v_number is not null then return 1; else return 0; end if; EXCEPTION WHEN OTHERS THEN return 0; end; /-- 小数点可以在最后一位 select * from t where is_number(col_name)=1;
我也来说种ascii码的 0 是48 9是57 flag := TRUE; for i in 1..12 loop if ascii(substr(str, i, 1)) < 48 or ascii(substr(str, i, 1)) > 57 THEN flag := FALSE; end if; end LOOP;
或者用translate转换一下
is_number 这是别人写的函数,你的库里没有这个函数,肯定报错了 。
select *
from tab
where REGEXP_LIKE(c,'[:digit:]');
return number
is
v_number number;
begin
select to_number(i_str) into v_number from dual;
if v_number is not null then
return 1;
else
return 0;
end if;
EXCEPTION
WHEN OTHERS
THEN
return 0;
end;
/
select is_number('123') from dual;
select is_number('123dd') from dual;
select is_number('123.44') from dual;
CREATE OR REPLACE FUNCTION IS_NUMBERIC(MyStr VARCHAR2)
RETURN varchar2
IS
ISNUM NUMBER;
BEGIN
ISNUM:=TO_NUMBER(MyStr);
RETURN ISNUM||' is number';
EXCEPTION
WHEN INVALID_NUMBER THEN
RETURN MyStr|| 'is not number';
WHEN OTHERS THEN
RETURN MyStr||' is not number';
END;接下来执行 select IS_NUMBERIC('9') from dual; 看看
from t
where trim(translate(col_name,'0123456789.',' ')) is null;
create table t(col_name varchar2(20));
insert into t(col_name) values('123.44');
insert into t(col_name) values('dd123.44');
insert into t(col_name) values('12bb3.44');
insert into t(col_name) values('884.54');
insert into t(col_name) values('999.');
commit;select col_name
from t
where trim(translate(col_name,'0123456789.',' ')) is null
and length(col_name)-length(replace(col_name,'.',''))<=1 -- 只允许有一个小数点(.)字符
and substr(col_name,length(col_name),1)<>'.'; -- 小数点不能在最后一位
select col_name
from t
where trim(translate(col_name,'0123456789.',' ')) is null
and length(col_name)-length(replace(col_name,'.',''))<=1; -- 只允许有一个小数点(.)字符-- 方法二:用函数
create or replace function is_number(i_str varchar2)
return number
is
v_number number;
begin
select to_number(i_str) into v_number from dual;
if v_number is not null then
return 1;
else
return 0;
end if;
EXCEPTION
WHEN OTHERS
THEN
return 0;
end;
/-- 小数点可以在最后一位
select * from t
where is_number(col_name)=1;
0 是48 9是57
flag := TRUE;
for i in 1..12 loop
if ascii(substr(str, i, 1)) < 48 or ascii(substr(str, i, 1)) > 57 THEN
flag := FALSE;
end if;
end LOOP;