Parsing a string in postgresql

Question

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?

Tim Biegeleisen · Accepted Answer · 2021-11-03 12:15:13Z

3

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;

edited Nov 3, 2021 at 12:15

answered Nov 3, 2021 at 11:05

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

Jim Jones Over a year ago

You won't get a better answer than this one ;) +1 However, if you want a "non-regex" solution check this

kannappan Over a year ago

Thanks Tim, this works for me! Probably I need to go through regex in postgresql

kannappan Over a year ago

Is there a way to return the value 0 if the REGEXP_REPLACE returns you only string, for example in case of my name is jerry the REGEXP_REPLACE will return my name is jerry instead of that can i return a value 0?

Tim Biegeleisen Over a year ago

@kannappan I have given you a version which will behave as you expect.

Jim Jones Over a year ago

@kannappan you can achieve by checking if the result is a number (if applicable) within a case. check this fiddle

Ajax1234 · Accepted Answer · 2021-11-03 11:49:44Z

1

You can also use regexp_matches for a shorter regular expression:

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

answered Nov 3, 2021 at 11:49

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Collectives™ on Stack Overflow

Parsing a string in postgresql

2 Answers 2

Demo

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related