sqlamazon-athenaprestotrino

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

Solution

  • 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