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