postgres regexp_replace negate presence of consecutive groups of chars

Question

i have a database export that come with some wrong chararacter substitution(like è => e'). So i have to change it back in postgres.
I'm going crazy to make an all in one regex that catch something like this:

cassine' de' pecche' e'

it must become

cassinè de' pecchè è

(de' must not change).

I managet to get it with two pass:

UPDATE mytable SET comun1=UPDATE mytable SET comun1=regexp_replace(column1,'([^dnNn])(e\'')', '\1\2è', 'g');   

UPDATE mytable SET comun1=UPDATE mytable SET comun1=regexp_replace(column1,'([^\s])([dnNn])(e\'')', '\1\2è', 'g');

Basically i want to exclude from replace a space followed by d or n and followed by e' (like " de'") and change the e' in all other cases.
I tried (?!\s[nNdD])(e\'') but it still changes " de'" to " dè"

Does anyone have a solution for this?

(?!...) looks forward not backward, and the lookbehind doesn't exist in postgresql regex flavor. — Casimir et Hippolyte
– Casimir et Hippolyte, Commented Feb 11, 2015 at 22:22

Ben Grimm · Accepted Answer · 2015-02-11 22:25:29Z

1

select regexp_replace($$cassine' de' pecche' e'$$, $$(\s[^dn]?|\w\w)e'$$, '\1è', 'gi')
    regexp_replace    
----------------------
 cassinè de' pecchè è
(1 row)

Explanation:

(\s[^dn]?|\w\w)e'
 ^       ^     ^ 
 |       |     followed by e'
 |       or 2 word chars
 space optionally followed by d or n

edited Feb 11, 2015 at 22:25

answered Feb 11, 2015 at 16:46

Ben Grimm

4,3912 gold badges18 silver badges24 bronze badges

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

7 Comments

Casimir et Hippolyte Over a year ago

perhaps could it be rewritten like this: (\s[^dn]?|\B\w)e'

Ben Grimm Over a year ago

That misses the replacement in longer words ending in e'. (e.g. cassine')

Casimir et Hippolyte Over a year ago

No, why? since I use the \B (non-word boundary anchor), cassine' is matched.

Ben Grimm Over a year ago

In PostgreSQL regex syntax, "\B is a synonym for backslash () to help reduce the need for backslash doubling"

Casimir et Hippolyte Over a year ago

You are right, it's probably because the word boundary is written in a different way with postgresql. After looking for more informations, it seems that the postgresql regex engine is build as the Henry Spencer's regex engine for TCL. So it has three different flavors (basic, extended, advanced). In the advanced syntax \B is replaced by \Y.

|

Collectives™ on Stack Overflow

postgres regexp_replace negate presence of consecutive groups of chars

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related