CODEWITHSUNDEEP
sqlregexpostgresql
akhi
akhi

Reputation: 692

Postgres regular expression in where clause

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?

Upvotes: 1

Views: 527

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6140

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

Upvotes: 2

Related Questions