CODEWITHSUNDEEP
regexpostgresql
Bhindi
Bhindi

Reputation: 1509

Postgresql regexp_replace negative lookahead not working

I am trying to replace street with st only if street isn't followed by any alphabet. Replacement is allowed if after street EITHER there is non-alphabet character OR end of string.

I am trying to achieve this in Postgresql 9.5 regex_replace function. Sample query i wrote:

select regexp_replace('super streetcom','street(?!=[a-z])','st');

street here shouldn't have been replaced by st since street is followed by 'c'. So the expected output is 'super streetcom' but the output i am getting is 'super stcom'.

Any help for why i am getting the unexpected output and what can be the right way to achieve the intended result.

Upvotes: 2

Views: 1431

Answers (2)

randomuser
randomuser

Reputation: 1221

This looks like a syntax issue. Try: ?! instead of ?!= . e.g.

select regexp_replace('super street','street(?![a-z])','st');

will return

super st

Upvotes: 1

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627292

A lookahead construct looks like (?!...), all what follows ?! is a lookahead pattern that the engine will try to match, and once found, the match will be failed.

It seems you need to match a whole word street. Use \y, a word boundary:

select regexp_replace('super streetcom street','\ystreet\y','st');

See the online demo

enter image description here

From the docs:

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

Upvotes: 1

Related Questions