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