How to escape single quotes in RLIKE Hive? Double single quotes not working

Question

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?

Answer 1

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

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