Postgres regular expression in where clause

Question

Is there a solution in postgres for matching as per below example:

'test' table entries:

id |                            url                             
----+-------------------------------------------------------------
 1  | /services/system
 2  | /services/system/{uuid}/group
 3  | /services/system/{uuid}/group/{uuid}/port

I want to match the following input strings against the url column that exist in table:

1. /services/system/1/group          --> should match row 3
2. /services/system/1/group/2/port   --> should match row 3
3. /services/system/1/group          --> should match row 2
4. /services/system/1/group/2        --> should not match
5. /services/system/1                --> should not match

I tried following query to match the match the 3rd row, but it did not work:

select * from test where regexp_replace(url, '{uuid}', '*', 'g') ~ '/services/system/1/group/1/port'

Any solution?

* isn't the wildcard character in a regex. You need to use .+ or maybe [0-9]+ to only match numbers — user330315
– user330315, Commented Jan 13, 2021 at 7:37
okay but '.+' didn't work either. Also the uuid placeholder may not necessarily be number. /1/ and /2/ are just used to simplify the example. — akhi
– akhi, Commented Jan 13, 2021 at 7:50
I think you should try like this.. select * from test where regexp_replace('/services/system/1/group/1/port', '[0-9]+', '{uuid}', 'g') = url — Akhilesh Mishra
– Akhilesh Mishra, Commented Jan 13, 2021 at 7:51
@AkhileshMishra As I said, {uuid} is not a number. Also the input string is dynamic in nature, so this kind of matching isn't a generic solution — akhi
– akhi, Commented Jan 13, 2021 at 7:59

Akhilesh Mishra · Accepted Answer · 2021-01-15 09:28:35Z

2

Considering your scenarios given in question and comments you can use below query:

select * from test 
where '/services/system/1/group' ~ concat('^',replace(url,'{uuid}','[^/]+'),'$')

Here it will check for any character except / in place of {uuid}

Edit If you want only alphanumeric then you should use try this:

select * from test 
where '/services/system/1/group' ~ concat('^',replace(url,'{uuid}','[\w]+'),'$')

DEMO

edited Jan 15, 2021 at 9:28

answered Jan 13, 2021 at 9:02

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

5 Comments

akhi Over a year ago

This query works! Thanks for the solution.

akhi Over a year ago

This query matches only when {uuid} is single digit. For e.g: it does not match id=3 for the following query in your demo example: select * from test where '/services/system/1001/group/2001/port' ~concat('^',replace(url,'{uuid}','[^/]'),'$')

akhi Over a year ago

shouldn't replacing '{uuid}' with '[\w+]' had worked? my {uuid} is alphanumeric.

akhi Over a year ago

downvoting as this is not a generic solution.

Akhilesh Mishra Over a year ago

actually I missed + in it. it should be [^/]+ . If you want to restrict to alphanumeric then you can use [\w]+. But if it is a statdard uuid then it should be '[\w-]+`. I have updated the answer

Collectives™ on Stack Overflow

Postgres regular expression in where clause

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related