REGEXP_SUBSTR转换后的输出不会转换为整数 [英] REGEXP_SUBSTR converted output is not casting as integer
问题描述
我尝试使用下面的REGEXP_SUBSTR Sql函数从20个字符的字符串中提取所有数字。
I tried extracting all the digits out of a 20 character string by using REGEXP_SUBSTR Sql function like below.
select
REGEXP_SUBSTR(substring(mycolumn,1,20), '^[0-9]', 1)
|| REGEXP_SUBSTR(substring(mycolumn,1,20), '^[0-9]', 2)
|| REGEXP_SUBSTR(substring(mycolumn,1,20), '^[0-9]', 3)
...
...
|| REGEXP_SUBSTR(substring(mycolumn,1,20), '^[0-9]', 20)
from tbl;
但是当尝试将其转换为bigint / decimal或任何数字数据类型时,它会失败类型为数字的输入语法无效
或无效数字,值'2',Pos 0,类型:长
和等等。
But when trying to cast it as bigint / decimal or any numeric data type it is failing with Invalid input syntax for type numeric
or Invalid digit, Value '2', Pos 0, Type: Long
and so on.
我错过了什么吗? REGEXP_SUBSTR连接的典型输出是105622,0044,022等。查询在Redshift数据仓库上运行,并且REGEXP_REPLACE / TRANSLATE尚未出现在那里。
Am I missing some thing? Typical output of the REGEXP_SUBSTR concatenation are 105622,0044,022 etc. The query ran on Redshift datawarehouse and REGEXP_REPLACE/TRANSLATE is not yet present there.
推荐答案
看起来每当字符串中没有数字时,regexp_substr s的上述连接就会返回空字符串(''),不支持转换为整数。所以我使用了 NULLIF(正则表达式,'')
Looks like whenever there are no digits in the string, then the above concatenation of regexp_substr s is returning empty string ('') which is not supported for casting into integer. So I used a NULLIF (regex expression, '')
这篇关于REGEXP_SUBSTR转换后的输出不会转换为整数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!