REGEXP_SUBSTR converted output is not casting as integer

Question

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.

androboy · Accepted Answer · 2014-03-27 20:12:40Z

2

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

answered Mar 27, 2014 at 20:12

androboy

8551 gold badge12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

REGEXP_SUBSTR converted output is not casting as integer

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related