CODEWITHSUNDEEP
sqlregexpostgresql
Sam
Sam

Reputation: 6618

Regex in Postgres - not doing what I'm trying to do (newbie 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.

Upvotes: 1

Views: 448

Answers (1)

SpliFF
SpliFF

Reputation: 39014

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.

Upvotes: 4

Related Questions