oracle查询字符串中的数字

-- 方法一：直接用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;