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?
To escape special characters in LIKE use ESCAPE parameter:
Wildcard characters can be escaped using the single character specified for the
ESCAPEparameter.
WITH dataset (str) AS (
VALUES ('sample_text '),
('sample text ')
)
SELECT *
FROM dataset
WHERE str like 'sample\_text%' ESCAPE '\'
Output:
| str |
|---|
| sample_text |