sql - Oracle SQL 将字符串转换为数字，异常(exception)情况是将文本视为 0

Question

我想将字符串列转换为数字，并能够在 Oracle SQL 中处理异常，例如文本。

目标是将文本转换为0，将字符串格式的数字转换为数字。

示例输入:

ID   Column1
1    01A
2    02A
3    1.30
4    1,30
5    100000
6           (Note: 1 empty space)
7           (Note: Null)

预期输出

ID   Column1
1    0
2    0
3    1.3
4    1.3
5    100000
6    0
7    0

我尝试了以下 SQL 命令:

select ID, to_number(Column1) As Column1 
from Table1

如果有任何非数字输出，则错误代码为 ORA-01722。

预期的结果是消除错误 ORA-01722，即使输入有 null、空格、文本(即任何非数字)

Answer 1

这需要两个措施:

1. 检查是否有任何非数字字符(请参阅此 solution)
   您可以使用正则表达式函数代替 TRANSLATE，但我希望它们会更慢。
2. 转换为数字，同时接受“,”和“.”作为小数点标记(参见 solution )

查询:

WITH test_data AS
(
  SELECT '01A' AS column1 FROM dual UNION ALL
  SELECT '02A' AS column1 FROM dual UNION ALL
  SELECT '1.30' AS column1 FROM dual UNION ALL
  SELECT '1,30' AS column1 FROM dual UNION ALL
  SELECT '100000' AS column1 FROM dual UNION ALL
  SELECT ' ' AS column1 FROM dual UNION ALL
  SELECT NULL AS column1 FROM dual
)
SELECT
    '''' || column1 || '''' AS column1, -- only to show result, distinct between '' and ' '
    -- Check if there are only numeric characters
    CASE WHEN TRIM( TRANSLATE( column1, '0123456789-,.', ' ') ) IS NULL
      THEN
          NVL(  -- replace NULL by 0
              TO_NUMBER(
                  -- both ',' and '.' should work as decimal marker so replace
                  REPLACE( TRIM( column1 ), ',', '.' ),
                  -- second parameter necessary to allow third
                  '99999999999999999999D99999999999999999999',
                  -- do not rely on NLS settings, use '.' as decimal marker
                  'NLS_NUMERIC_CHARACTERS=''. '''
              ),
          0 )
      ELSE 0  -- default value if not numeric
    END AS result
FROM test_data;

结果:

COLUMN1      RESULT
-------- ----------
'01A'             0
'02A'             0
'1.30'          1.3
'1,30'          1.3
'100000'     100000
' '               0
''                0