我的数据库中有一个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 |