Thread: Help with regexp-query

I am struggling a bit to do the following type of update in a table.

I want the content of a field updated like this:

Original:  
'||||0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'

After update:'|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'

in other words: change all multiple adjacent occurences of '|' to only 1. 

I have tried the following query but it fails:

select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
akb_articles limit 100

This ends with 'ERROR: invalid regular expression: quantifier operand
invalid'.

I would apreciate some help with this one please.

Regards
Johann
-- 
Johann Spies                            Telefoon: 021-808 4699
Databestuurder /  Data manager

Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology 
Universiteit Stellenbosch.
    "If any of you lack wisdom, let him ask of God, that      giveth to all men liberally, and upbraideth not; and
itshall be given him."              James 1:5 
 

Johann Spies <jspies@sun.ac.za> wrote:

> I am struggling a bit to do the following type of update in a table.

> I want the content of a field updated like this:

> Original:
> '||||0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'

> After update:
>  '|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'

> in other words: change all multiple adjacent occurences of '|' to only 1.

> I have tried the following query but it fails:

> select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
> akb_articles limit 100

> This ends with 'ERROR: invalid regular expression: quantifier operand
> invalid'.

> I would apreciate some help with this one please.

You need to double the backslashes (e. g. "E'\\|{2,}'");
otherwise the parser will "eat" the first backslash and pass
just "|{2,}" as the second argument to regexp_replace().

Tim