How can I escape double and single quotes in google sheets query?

user_4136605 0
Viewed 4381

I have this query in google sheets cell:

=QUERY(Sheet1!A2:F, "SELECT * WHERE A='te'xt"text'", 0)

I can escape double quote like this: text""text

But how can I escape double and single quote? te'xt"text

google-sheets-query google-sheets-formula google-sheets
1 Answers

One possible alternative would be to do some pre-processing (before the query)

=ArrayFormula(QUERY({regexmatch(Sheet1!A2:A, "te\'xt"&char(34)&"text"),Sheet1!A2:F}, "Select Col2, Col3, Col4, Col5, Col6,Col7  where Col1 = true", 0))

The regex match will return 'true' for all rows matching the pattern. Then the query() selects only the rows with 'true' in Col1.

user_38894230
Related