CODEWITHSUNDEEP
sqlregexcastingintegeramazon-redshift
androboy
androboy

Reputation: 845

REGEXP_SUBSTR converted output is not casting as integer

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;

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.

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.

Upvotes: 2

Views: 1474

Answers (1)

androboy
androboy

Reputation: 845

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, '')

Upvotes: 2

Related Questions