How can I escape an apostrophe inside a SQL query in Google Sheets?

Question

Here's my function:

=QUERY(QUERY(IMPORTRANGE("XXXXXXX", "XXXXXX!A1:C"),"SELECT * WHERE Col2 = 'Woman's blabla: blablabla'"),"SELECT Col1, Col2")

I've tried adding another apostrophe:

=QUERY(QUERY(IMPORTRANGE("XXXXXXX", "XXXXXX!A1:C"),"SELECT * WHERE Col2 = 'Woman''s blabla: blablabla'"),"SELECT Col1, Col2")

No luck.. and I've tried putting a backslash, no luck as well:

=QUERY(QUERY(IMPORTRANGE("XXXXXXX", "XXXXXX!A1:C"),"SELECT * WHERE Col2 = 'Woman/'s blabla: blablabla'"),"SELECT Col1, Col2")

EDIT: The locale of the document is UK.

it looks like your using a forward slash in your example instead of a backslash — Aurielle Perlmann, Jan 25 '16 at 15:32

JPV · Accepted Answer · 2020-03-30T13:20:29.503

20

Try using double quotes around the word with the apostrophe

"SELECT * WHERE Col2 = ""Woman's blabla: blablabla"""

edited Mar 30 '20 at 13:20

answered Jan 25 '16 at 14:09

JPV

26,499
4
33
48

Edited my answer.. try again ? If if still doesn't work, can you share some sample data ? – JPV Jan 25 '16 at 19:19
This syntax is difficult to understand for me, but I just assumed that If I substituted the text for a variable (named/range) it was going to be fine, and it is. this is the same statement with the variable: "SELECT * WHERE C="""&Enemy&"""" – White_King Jan 19 '21 at 09:03

score 11 · Answer 2 · answered Feb 28 '17 at 10:54

11

If the string you're trying to match is in a cell, try surrounding the cell name with """" like so:

=QUERY(Foo!A:B,"select A where B = " & """" & A1 & """" & "")

answered Feb 28 '17 at 10:54

Nick Bolton

38,276
70
174
242

1

There's a slightly simpler version of this too: `=QUERY(A:B, "SELECT A WHERE B = """ & C1 & """")`. Note 4x quotes at the end. – Ryan Cole Nov 16 '20 at 23:31

score 2 · Answer 3 · answered Sep 27 '20 at 16:56

Trying to generate an output like SELECT * WHERE Col2 = "Woman's blabla: blablabla" seems to be a valid solution. However, as mentioned, escaping characters with backslash \ does not work for Google Sheets.

Searching for a workaround, I came across this link. It suggests using char(34) in place of double quotes, which is the working solution for me.

Accordingly, you can write

=QUERY(QUERY(IMPORTRANGE("XXXXXXX", "XXXXXX!A1:C"),"SELECT * WHERE Col2 = " & char(34) & "Woman's blabla: blablabla" & char(34)),"SELECT Col1, Col2")

score 1 · Answer 4 · answered Jan 25 '16 at 21:00

Try substituting the double quotes in the source data through the function, you may need to swap out some of the quotes i added - I dont know what the text your searching for actually looks like in your source data:

=QUERY(QUERY(IMPORTRANGE("XXXXXXX", SUBSTITUTE("XXXXXX!A1:C","""","'")),"SELECT * WHERE Col2 = 'Woman's blabla: blablabla'"),"SELECT Col1, Col2")

EDIT:

How can I escape an apostrophe inside a SQL query in Google Sheets?

4 Answers4

Linked