Postgresql regexp_replace negative lookahead not working

Question

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.

Are you sure (?!=pattern) is the correct negative lookahead syntax in postgresql? Usualy it is just (?!pattern) — Sebastian Proske
– Sebastian Proske, Commented Jun 30, 2017 at 11:11
Ohhh.. Found my mistake. negative lookahead should have been (?!pattern). Thanks @SebastianProske — Bhindi
– Bhindi, Commented Jun 30, 2017 at 11:14

Wiktor Stribiżew · Accepted Answer · 2017-06-30 11:17:04Z

1

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

From the docs:

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

answered Jun 30, 2017 at 11:17

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

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

Comments

randomuser · Accepted Answer · 2017-06-30 11:19:29Z

1

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

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

will return

super st

edited Jun 30, 2017 at 11:19

answered Jun 30, 2017 at 11:14

randomuser

1,2212 gold badges10 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgresql regexp_replace negative lookahead not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related