How to Presto Escape a Single Quote
In reality there will be some occasions when you need to use a quote character in your data, in your query, or in result sets, but you want the SQL interpreter to treat the quote as “just another character”, as opposed a quote which has special meaning – namely to denote a string literal in most SQL dialects. This is called escaping a character.
Some SQL dialects use a backslash as the escape character. Presto’s SQL needs no escape character; instead it uses a double character notation. Here’s a simple example of what I mean:
presto:default> select 'hello world'; _col0 ------- hello world
What if I want single quotes to actually appear in the result? I need to do this in Presto:
presto:default> select '''hello world''' as col1; col1 --------- 'hello world'
I have simply used two single quotes to tell the interpreter the places where “I really want to include a single quote”.
Another example: What if there are single quotes in my table?
presto:default> select * from mytable where column_a in ('Driver's License'); Query n failed: line 1:43: mismatched input 'in'. Expecting: 'AND', 'EXCEPT', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION', <EOF>
The query was rejected because the interpreter encountered an unexpected single quote in “Driver’s License”. To handle this we use two single quotes:
presto:default> select * from mytable where column_a in ('Driver''s License');
(query runs ok)
So that’s how to escape a single quote in Presto.