CODEWITHSUNDEEP
phpsqlregexpostgresql
Marcelo Assis
Marcelo Assis

Reputation: 5194

Postgres Regex not working as expected

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?

Upvotes: 0

Views: 1013

Answers (1)

brandonscript
brandonscript

Reputation: 72875

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]+

Upvotes: 1

Related Questions