Regex in Postgres - not doing what I'm trying to do (newbie question)

Question

I have this regex in a query in postgres and I cannot figure out why it is not matching anything after the text specified in the regex;

The idea is removing the last part, including the separator characters between.

I have records like these to match:

Villa hermosa, Pilar, PCIA. BS. AS.    
Esmeralda - Pilar - BUENOS AIRES.    
San Martin, BUENOS AIRES.-

and I'm using this expression:

 regexp_replace(location,
 '([,\s\.-]*PCIA. BS. AS[,\s\.-]*|
   [,\s\.-]*BUENOS. AIRES[,\s\.-]*$|
   [,\s\.-]*BS. AS[,\s\.-]*$|
   [,\s\.-]*P.B.A[,\s\.-]*$)', '' )

this is working fine from the text PCIA, BUENOS, but it is not taking the ',' '.' the '-' nor spaces after the word. I need help finding where the problem is.

Craig Ringer · Accepted Answer · 2012-09-17 04:20:00Z

4

Double your backslashes. \ => \\

Postgres thinks you're doing escapes on the string itself.

In newer PostgreSQL versions where standard_conforming_strings is on by default it is no longer necessary to double backslashes unless you're using an E'string' or have explicitly set standard_conforming_strings to off.

edited Sep 17, 2012 at 4:20

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered May 22, 2009 at 3:43

SpliFF

39.1k16 gold badges95 silver badges122 bronze badges

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

1 Comment

Sam Over a year ago

Thank you very much! (I'm sure I tried it once, but for sure another error prevented that to work and I though it was not the cause)

Collectives™ on Stack Overflow

Regex in Postgres - not doing what I'm trying to do (newbie question)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related