Postgres Regex not working as expected

Question

I have a query which loads a lot of fields, and removes duplicate spaces, tabs and line breaks.

regexp_replace(FIELD, E'[\|\\s\\n\\r]+', ' ', 'g' ) as FIELD

When I test this using Sql Manager for PostgreSQL (Windows Environment), it works as expected. But...

But this query is at a PHP file, which is ran daily using crontab (Linux Environment), it remove duplicate spaces, tabs, line breaks and "s"

Example, the string ahead:

"Small 

unicorns are   smart"

Turns into:

"mall unicorn are mart"

Why is that happening?

You probably need to double escape those \'s, or remove the double escaping, without the actual code it is hard to tell. What do you see if you just echo your query? — Wrikken
– Wrikken, Commented Jan 29, 2014 at 21:01

brandonscript · Accepted Answer · 2014-01-29 21:27:47Z

1

You don't want to double escape the \ in php (unlike, say, Java). You're actually escaping the backslash, turning the special characters into literal letters. Or, if I'm totally wrong, you actually need a double escape.

Try:

[\|\s\n\r]+

Also, as Maxim points out, \r and \n are already included in \s.

Edit:

looks like you're trying to include a literal pipe in there too. In that case, use [|\s]+ http://regex101.com/r/nE3dI8

If you need it double escaped, [|\\\s]+

edited Jan 29, 2014 at 21:27

answered Jan 29, 2014 at 21:19

brandonscript

73.7k35 gold badges178 silver badges240 bronze badges

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

7 Comments

mu is too short Over a year ago

But "\" has a meaning inside a E'' string in PostgreSQL so you need to double them to get a single "\" down to the PostgreSQL regex engine. Then you probably have to escape each of those to get them past PHP.

brandonscript Over a year ago

@muistooshort blast, you're probably right. So probably needs to be double escaped [|\\\s]+

mu is too short Over a year ago

Best bet is to experiment on sqlfiddle.com to get the right number of escaped escapes and then double them to get them through PHP. What a mess.

brandonscript Over a year ago

@muistooshort I think perhaps \\\\\\\\\\\\\\\\\\\\\ :P

brandonscript Over a year ago

@muistooshort can I wrap it in Haskell generated by COBOL too?

|

Collectives™ on Stack Overflow

Postgres Regex not working as expected

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