CODEWITHSUNDEEP
sqlamazon-athenaprestotrino
ericbrownaustin
ericbrownaustin

Reputation: 1300

Athena/Presto Escape Underscore

I'm trying to escape an underscore in a like operator but not getting any results. I'm trying to find any rows with a value like 'aa_'.

WHERE value LIKE '%aa\\_%'

Upvotes: 3

Views: 3062

Answers (1)

Guru Stron
Guru Stron

Reputation: 142058

Use ESCAPE:

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

WITH dataset (str) AS (
    VALUES ('aa_1'),
        ('aa_2'),
        ('aa1')
)
SELECT *
FROM dataset 
WHERE str like 'aa\_%' ESCAPE '\'

Output:

str
aa_1
aa_2

Upvotes: 8

Related Questions