escape single quote in s3 select query

Question

Sample data, stored in a file in S3. As you can see the format of my data is one json per line

{"first":  "John", "last": "Smith"}
{"first":  "Mary", "last": "O'Hara"}
{"first":  "Mary", "last": "Oats"}

My ultimate objective is to query by the last name and using the like operator together with a user provided substring. So I go step by step from easy to difficult:

This query works and returns all rows:

select s.* from s3object s 

Good! Let's continue. The next query I tried works and returns, as expected, John Smith

select s.* from s3object s where s."last" = 'Smith'

The next step is to try by a substring of the surname. Let's find all persons whose last name starts with an "O".

select s.* from s3object s where s."last" like 'O%';

This works and returns the two Marys in my dataset.

The next step is the one that doesn't work. I want to find all users whose last name starts with an O and an apostrophe. This I can't make to work. I tried:

select s.* from s3object s where s."last" like 'O'%'
select s.* from s3object s where s."last" like 'O\'%'
select s.* from s3object s where s."last" like "O'%"

None of them works. How can I put a single quote (') inside a string literal in s3 select?

Answer 1

You just need to use two single quotes

SELECT *
FROM Test.testing
WHERE "last" = 'O''Hara';