我的数据库中有一个varchar列。它应该有“43”、“43000”、“50000”等值,但有时它有“43000”、“50”、“00000”等值,有时它还有包含其他字符的垃圾值。
我要做的是首先替换所有值中的',',然后尝试将其转换为int,如果成功,则返回此int值。否则返回null。我试图为它编写一个Python UDF,但它似乎不起作用。这里是:

create or replace function isnumeric (aval VARCHAR(20000))
  returns int
IMMUTABLE
as $$
    try:
       aval = aval.replace(',','');
       x = int(aval);
       return x;
    except:
       return (1==2);
       return null;

$$ language plpythonu;

select

isnumeric(field1)

from
table
limit 10000

有人能帮我吗??

最佳答案

在我看来你不需要自定义项。您可以使用REGEXP_INSTR查找无效值,然后使用REGEXP_REPLACE清除有效字符串中的非数字字符。

WITH test_vals AS
    (          SELECT 'garbage_1' test,   'xx43,000' val
     UNION ALL SELECT 'decimal'   test,  '43,000.00' val
     UNION ALL SELECT 'commas'    test,  '50,00,000' val
     UNION ALL SELECT 'date_val'  test, '2019/03/03' val
)
SELECT test
       --Any character other than numbers, commas or decimal returns NULL
     , CASE WHEN REGEXP_INSTR(val,'[^0-9,.]') > 0 THEN NULL
       --Commas are removed, decimal marker is retained
       ELSE REGEXP_REPLACE(val,'[^0-9.]','') END::NUMERIC  AS ouput
FROM test_vals
;

输出为:
   test    |  ouput
-----------+---------
 garbage_1 |
 decimal   |   43000
 commas    | 5000000
 date_val  |

10-08 03:56
查看更多