Question

oracle数据库中字段转换，找出一列中不可转为数字的行

有一列数字，数据库中存储方式为varchar2 ，形式类似为‘2456.00’，‘456.15’将他们转换为数字的过程中，因为有些数据不规范，类似‘20.20.20’或者‘20万’或‘2O’等，导致无法正常的转换，请问怎么找到这些不规范的字段。

Answer 1

oracle中没有现成的方法判断数据是否是数字类型的的，可以通过function来实现：

CREATE OR REPLACE FUNCTION isnum (str IN VARCHAR2)
   RETURN NUMBER
IS
   v_str VARCHAR2 (1000);
BEGIN
   IF str IS NULL
   THEN
      RETURN 0;
   ELSE
      v_str := TRANSLATE (str, '.0123456789', '.');
      IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL
      THEN
         RETURN 1;
      ELSE
         RETURN 0;
      END IF;
   END IF;
END isnum;

扩展判断一列的数据类型是不是数值类型:

select * from 表 where not regexp_like(列名,'^[[:digit:]]+$');.

Answer 2

新建一个函数，用来判断是否数字
Oracle 中 isnumeric 的三种实现
在 oracle 中没有现成的判断是否为数字函数，下面就用三种方法来实现： 　
1. 利用 to_number
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
v_str FLOAT;
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
BEGIN
SELECT TO_NUMBER (str)
INTO v_str
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN 0;
END;
RETURN 1;
END IF;
END isnumeric;
2. 利用 regexp_like
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
IF regexp_like (str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END IF;
END isnumeric;
3. 利用 TRANSLATE
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
v_str VARCHAR2 (1000);
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
v_str := TRANSLATE (str, '.0123456789', '.');
IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END IF;
END isnumeric;

Answer 3

其实很简单的，只要判断字段中是否包含两个及两个以上的点，转换的时候将其排除就行了
具体写法是
select …… from tbname t
where t.col not like '%.%.%'

我写了个例子做了下验证
select * from
(select '20.20.20' col from dual
union all
select '20.20' col from dual
union all
select '10.20.20.20' col from dual
union all
select '20.20.20.20.29' col from dual
)
where col like '%.%.%'
结果
20.20.20
10.20.20.20
20.20.20.20.29

Answer 4

新建一个函数，用来判断是否数字
Oracle 中 isnumeric 的三种实现
在 oracle 中没有现成的判断是否为数字函数，下面就用三种方法来实现： 　
1. 利用 to_number
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
v_str FLOAT;
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
BEGIN
SELECT TO_NUMBER (str)
INTO v_str
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN 0;
END;
RETURN 1;
END IF;
END isnumeric;
2. 利用 regexp_like
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
IF regexp_like (str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END IF;
END isnumeric;
3. 利用 TRANSLATE
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
v_str VARCHAR2 (1000);
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
v_str := TRANSLATE (str, '.0123456789', '.');
IF v_str = '.' OR v_str = '+.' OR v_str = '-.' OR v_str IS NULL
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END IF;
END isnumeric;

Answer 5

尝试一下ORDER BY

追问

order by 不行的，因为order by 是按顺序一个个比较字符的。数据量很大，几万条，自己去找不现实。解决这个问题，最好从正则表达式入手，把数据分为两部分，一个是点和数字，另一部分是补集。然后再怎么处理第一部分是难点，现方向是通过存储过程捕获异常处理。还是希望有能力的人能帮忙认真解答下，因为本人水平有限。

追答

既然XX.XX的形式是正确的，这样行不呢：
select * from TABLE where 字段 like %.% and （字段 not like %.%.%)
不过要再考虑一下如何排除类似于 2O.00 这样的值。