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

Question

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

H. Pauwelyn · Accepted Answer · 2022-02-02 08:09:20Z

1

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

edited Feb 2, 2022 at 8:09

H. Pauwelyn

14.4k28 gold badges93 silver badges164 bronze badges

answered Jun 2, 2020 at 0:03

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

rshar Over a year ago

Whenever I am using that I get this error '[Code: 0, SQL State: 2201B] ERROR: invalid regular expression: quantifier operand invalid' What does that mean?

jjanes Over a year ago

@rshar It means your col4 contains some values (not the ones you show) which have characters which are meaningful to the regexp engine.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related