How do you escape underscores and single quotes in Amazon Athena queries?

Question

I would like to run a query like the following on Amazon Athena

Select * from my_table
where my_table.my_field like '%'sample_text'%'

I want to match the single quotes and the underscore in 'sample_text'.

I've tried variations of escape characters like \_, \\_, [_], `_, and `_` without success.

Is this possible?

Answer 1

To escape special characters in LIKE use ESCAPE parameter:

Wildcard characters can be escaped using the single character specified for the ESCAPE parameter.

WITH dataset (str) AS (
    VALUES ('sample_text '),
    ('sample text ')
)

SELECT *
FROM dataset 
WHERE str like 'sample\_text%' ESCAPE '\'

Output:

str
sample_text