CODEWITHSUNDEEP
regexpostgresqlsubstring
Stanislau Listratsenka
Stanislau Listratsenka

Reputation: 680

Exclude word and quotes from regexp

I have the following phrases:

  1. Mr "Smith"
  2. MrS "Smith"

I need to retrieve only Smith from this phrases. I tried thousands of variants. I stoped on (?!Mr|MrS)([^"]+). Help, please.

Upvotes: 0

Views: 327

Answers (1)

The fourth bird
The fourth bird

Reputation: 163372

The pattern (?!Mr|MrS)([^"]+) asserts from the current position that what is directly to the right is not Mr or MrS and then captures 1+ occurrences of any char except "

So it will not start the match at Mr but it will at r because at the position before the r the lookahead assertion it true.

Instead of using a lookaround, you could match either Mr or MrS and capture what is in between double quotes.

\mMrS? "([^"]+)"
  • \m A word boundary
  • MrS? Match Mr with an optional S
  • " Match a space and "
  • ([^"]+) capture in group 1 what is between the "
  • " Match "

See a postgresql demo

For example

select REGEXP_MATCHES('Mr "Smith"', '\mMrS? "([^"]+)"');
select REGEXP_MATCHES('MrS "Smith"', '\mMrS? "([^"]+)"');

Output

    regexp_matches
1   Smith

    regexp_matches
1   Smith

Upvotes: 1

Related Questions