Presto: Cannot escape '_' (underscore) in like statement.

[danielcweeks]

Escaping the underscore in a like statement results in no records being matched.

Example:
select * from mytable where category like '%\_common';

Will not match any results. Without the \, it will match _uncommon.

Escaping doesn't appear to be working properly.

[electrum]

Presto follows ANSI SQL for the LIKE operator: you must specify the escape character using the ESCAPE clause. There is no default as in some other databases.

'abc' LIKE 'a\_c' ESCAPE '\'

[wubiaoi]

How can I input a quote? I try ' ' to escape, but failed.

select '\'' ESCAPE '\' from test

[electrum]

@hellopower in standard SQL, single quotes are escaped by doubling them: SELECT 'it''s great'

[dlamblin]

Why is there no documentation within presto's SQL doc about LIKE?
This page mentions it, but that's it. https://prestodb.io/docs/current/functions/regexp.html
Can this comment be moved to a new issue?

[electrum]

We should document it here: https://prestodb.io/docs/current/functions/comparison.html

[oaustegard]

It's been 2.5 years plus and no movement on documenting LIKE and the wildcards it uses...

[rongrong]

@oaustegard do you want to submit a PR?

[oaustegard]

Might have to - meanwhile found the Test cases for the LIKE syntax - so that helps: https://github.com/prestodb/presto/blob/9aa8f80edf946d26760012d9da4c1b34cff25eb7/presto-main/src/test/java/com/facebook/presto/sql/TestExpressionInterpreter.java#L1223

Deep Link to code