How to use regexp in where clause to filter rows in Postgres?

user_12436050 0
Viewed 1351

I have following table in postgres 11.0

col1 col2 col3 col4
NCT00001723 4894402 xenical (orlistat)capsules xenical
NCT00001724 4894403 xenical (orlistat)capsules orlistat
NCT00001725 4894404 capsules capsules
NCT00001726 4894405 insulin ins

I would like filter above rows such that either col3 = col4 or col3 exact content should be contained in col4.

The desired output is:

col1 col2 col3 col4
NCT00001723 4894402 xenical (orlistat)capsules xenical
NCT00001724 4894403 xenical (orlistat)capsules orlistat
NCT00001725 4894404 capsules capsules

I am trying below query to get this output.

SELECT
    *
FROM
    table 
where col3 = col4 or                         --exact match
regexp_matches(col3, '(.*).*\(') = col4 or   --match content before brackets
regexp_matches(col3, '.*\(.*\).*') = col4 --match content in brackets

Any suggestions here will be really helpful. Thanks

where-clause select postgresql sql regex
1 Answers

If I followed you correctly, you could just use word boundaries escape \y:

\y: matches only at the beginning or end of a word

In your query:

select * from mytable where col3 ~  ('\y' || col4 || '\y')

Demo on DB Fiddle:

col1 col2 col3 col4
NCT00001723 4894402 xenical (orlistat)capsules xenical
NCT00001724 4894403 xenical (orlistat)capsules orlistat
NCT00001725 4894404 capsules capsules
user_106767160
Related