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.

Answer 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;