2

I would like to escape the single quote in RLIKE input. I used double single quotes like so:

SELECT * FROM TABLE
WHERE column RLIKE 'o''brien'

But it returned the results with "obrien" rather than "o'brien". I tried "\\'" instead of double single quotes too, but that doesn't work either. So what is the correct escape character for single quote?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Amelia
  • 33
  • 5

2 Answers2

2

Three methods:

1 Put the whole regexp into double-quotes, single quote is shielded inside double-quotes:

where column rlike "o'brien"

See also: https://stackoverflow.com/a/66175603/2700344

2 Use unicode \u0027

where column rlike 'o\\u0027brien'

3 Use HEX \x27

where column rlike 'o\\x27brien'

Using \\x or \\u codes you can check any special character if you know it's code.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
0

You can just use =:

WHERE column = 'o''brien'

I'm not sure why you are using RLIKE unless you intent:

WHERE column LIKE '%o''brien%'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I simplified my command, I want to look for multiple patters so Regex is the best option (i.e. "(o''brien)||(mcdonald)" ). BTW, LIKE exhibits the same behavior too. – Amelia Feb 21 '21 at 03:02