Postgres Query with Regex

Question

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...

You need to escape single quotes around mister_xx in your query. — Ihor Romanchenko
– Ihor Romanchenko, Commented May 21, 2013 at 5:46
Try to start from something simple. Like WHERE "Column" ~ '.*''mister_xx''.*'. It will find you any text, that has 'mister_xx' in it. Then just buid up your regexp from that point. — Ihor Romanchenko
– Ihor Romanchenko, Commented May 21, 2013 at 6:07
Your current regexp has too many errors to fix them at once. — Ihor Romanchenko
– Ihor Romanchenko, Commented May 21, 2013 at 6:08

Bohemian · Accepted Answer · 2013-05-21 12:09:36Z

1

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 .

answered May 21, 2013 at 12:09

Bohemian♦

427k103 gold badges603 silver badges749 bronze badges

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

Comments

SBaha · Accepted Answer · 2013-05-22 14:19:50Z

1

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}'

answered May 22, 2013 at 14:19

SBaha

832 silver badges7 bronze badges

Collectives™ on Stack Overflow

Postgres Query with Regex

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related