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?

a little confused, could you give some expected data examples? — LiuChang, Aug 31 '19 at 14:44
Simply use triple quotos """ for warp your query and use the single quoto as you wish. — Lamanus, Sep 01 '19 at 01:11

score 6 · Accepted Answer · answered Sep 02 '19 at 20:24

6

You just need to use two single quotes

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

answered Sep 02 '19 at 20:24

Tarun Lalwani

142,312
9
204
265

score 1 · Answer 2 · answered Sep 03 '19 at 13:39

1

Try out the following query:

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

To escape a single quote, you need to specify one more single quote before it.

answered Sep 03 '19 at 13:39

Abdullah Khawer

4,461
4
29
66

escape single quote in s3 select query

2 Answers2