How to extract only numerics from string in PostgreSQL 8.0.2 Amazon Redshift

Question

I only want the numeric part of this string column:

identity
student:1234
student:56
student:789
id:driver_license-111-AZ
id:learner_permit-222-NY

So that the output should be:

wanted
1234
56
789
111
222

I am using PostgreSQL 8.0.2 (Amazon Redshift) and I think SELECT REGEXP_SUBSTR(identity,'[0-9]') FROM table should work. But it does not. I tried multiple variations of optional arguments in the REGEXP_SUBSTR but I can't get it to work. Would someone please help me? With this function or otherwise.

Tim Biegeleisen · Accepted Answer · 2022-12-07 04:29:50Z

1

Well REGEXP_SUBSTR() should work assuming you use the correct regex pattern:

SELECT REGEXP_SUBSTR(identity, '[0-9]+')  -- [0-9]+ means one or MORE digits
FROM yourTable;

You might also be able to phrase this using a regex replacement:

SELECT REGEXP_REPLACE(identity, '[^0-9]+', '')  -- strip non digit characters
FROM yourTable;

answered Dec 7, 2022 at 4:29

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

How to extract only numerics from string in PostgreSQL 8.0.2 Amazon Redshift

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related