CODEWITHSUNDEEP
regexpostgresql
rshar
rshar

Reputation: 1477

How to ignore specific set of words or characters from pattern search in Postgres

I have following table in Postgres 11.

col1
BI Placebo
BI 10773 Placebo
AMG 386 Placebo
Eflornithine or Placebo
Sulindac;Placebo
Sulindac plus Placebo
Vehicle Controlled Placebo

I would like to fetch those rows where there are specific set of words and special characters ignored and a string has 1 or more before and after the pattern. The desired output is:

col1
BI Placebo
BI 10773 Placebo
AMG 386 Placebo
Vehicle Controlled Placebo

I tried following query to execute this:

select * from table where col1 ~* '^(?!or|;|+|plus)\w\s\w\sPlacebo$' or col1 ~* '^(?!or|;|+|plus)\w\sPlacebo$'

My apologies if it is not a complex query but at the moment I am unable to escape those words and characters.

Upvotes: 0

Views: 1370

Answers (1)

The fourth bird
The fourth bird

Reputation: 163362

If you allow to match word character only, you can omit matching ; and + from the alternation. Note that you have to escape the plus \+ or add both to a character class [;+]

You can check that the string does not contain or and plus using the negative lookahead (?!.*(?:or|plus))

Then match 1+ word chars using \w+ followed by optionally match a space and 1+ word chars.

Then match Placebo at the end of the string.

^(?!.*(?:or|plus))\w+(?: \w+)? Placebo$

Regex demo | Postgresql demo

For example

select * from table where col1 ~* '^(?!.*(?:or|plus))\w+(?: \w+)? Placebo$'

Output

col1

BI Placebo
BI 10773 Placebo
AMG 386 Placebo
Vehicle Controlled Placebo

Upvotes: 2

Related Questions