CODEWITHSUNDEEP
regexpostgresql
Très
Très

Reputation: 804

Postgres Query with Regex

I'm trying to create a regex to find (and then eventually replace) parts of strings in a PG DB. I'm using PSQL 9.0.4

I've tested my regex outside of PG and it works perfectly. However, it isn't playing well with PG. If anyone can help me understand what I'm doing wrong it would me much appreciated.

Regex:

{php}.*\n.*\n.*'mister_xx']\)\);.*\n} \n{\/php}

Postgres Query:

SELECT COUNT(*) FROM (SELECT * FROM "Table" WHERE "Column" ~ '{php}.*\n.*\n.*'mister_xx']\)\);.*\n} \n{\/php}') as x;

Postgres Response:

WARNING:  nonstandard use of escape in a string literal
LINE 1: ...M (SELECT * FROM "Table" WHERE "Column" ~ '{php}.*\n...
                                                             ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR:  syntax error at or near "mister_xx"
LINE 1: ..."Table" WHERE "Column" ~ '{php}.*\n.*\n.*'mister_x...

Upvotes: 1

Views: 1995

Answers (2)

SBaha
SBaha

Reputation: 83

You need to double escape the backslashes and add an E before the statement: SELECT * FROM "Table" WHERE "Column" ~ E'{php}\n.\n.*''mister_xx'']\)\);.*\n} \n{\/php}'

Upvotes: 1

Bohemian
Bohemian

Reputation: 425013

In SQL, quotes are delimited as two quotes, for example:

'Child''s play'

Applying this to your regex makes it work:

SELECT COUNT(*)
FROM "Table"
WHERE "Column" ~ '{php}.*\n.*\n.*''mister_xx'']\)\);.*\n} \n{\/php}' as x;

Note also how the redundant subquery .

Upvotes: 1

Related Questions