Matching special characters

Question

I want to match a list of special characters ('-&) for a postgresql query. Through an online regex editor I came to the pattern [\'\-\&\\]. But when I use that in my query I get an syntax error:

connection.execute("SELECT index, name FROM test21 WHERE test21.name  ~ '[\'''\-\&\\]'")

results ins

ProgrammingError: (ProgrammingError) syntax error at or near "\"
LINE 1: test21 WHERE test21.name  ~ '['''\-\&\]'
                                         ^

I found other answers, like https://stackoverflow.com/a/25925429/380038, but these match all special characters.

You need to handle any inside single-quotes, too. So: `'[\'''\-\&\\]'` — Nick, Aug 03 '16 at 17:48
Sorry, there was one extra quote. Should be: `'[\''\-\&\\]'` — Nick, Aug 03 '16 at 17:59

score 0 · Accepted Answer · answered Aug 03 '16 at 18:01

0

with q(str) as (
    values ('abc'), ('a-b'), ('a''b'), ('a&b'), ('a\b'))

select str, str ~ '[''\-&\\]' bool
from q;

 str | bool 
-----+------
 abc | f
 a-b | t
 a'b | t
 a&b | t
 a\b | t
(5 rows)

answered Aug 03 '16 at 18:01

klin

112,967
15
204
232

Only one addition: Because of the use of sqlalchemy there has to be another '\' : `'[''\-&\\\]'` worked for me in the end as part of the python string. – Framester Aug 04 '16 at 08:51

Matching special characters

1 Answers1