Parsing a string in postgresql-CodePudding

Let's say I have column of datatype varchar, the column contains values similar to these

'My unique id [john3 UID=123]'
'My unique id [henry2 UID=1234]'
'My unique id [tom2 UID=56]'
'My unique id [jerry25 UID=98765]'

How can I get only the numbers after UID= in the strings using postgresql. for eg in string 'My unique id [john3 UID=123]' I want only 123, similarly in string 'My unique id [jerry25 UID=98765]' I want only 98765 Is there a way in PostgreSQL to do it?

CodePudding user response：

We can use REGEXP_REPLACE here:

SELECT col, REGEXP_REPLACE(col, '.*\[\w  UID=(\d )\].*$', '\1') AS uid
FROM yourTable;

Demo

Edit:

In case a given value might not match the above pattern, in which case you would want to return the entire original value, we can use a CASE expression:

SELECT col,
       CASE WHEN col LIKE '%[%UID=%]%'
            THEN REGEXP_REPLACE(col, '.*\[\w  UID=(\d )\].*$', '\1')
            ELSE col END AS uid
FROM yourTable;

CodePudding user response：

You can also use regexp_matches for a shorter regular expression:

select regexp_matches(col, '(?<=UID\=)\d ') from t;