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.