My Problem

I have a Google Spreadsheet with two tabs - one containing regexes and values:

Regex sheet

And the other containing phrases which matches regex in the first tab, and a QUERY Formula that picks the right value from the regex column for each matching phrase:

=QUERY(Regex!A:B, "SELECT B WHERE lower('"&A1&"') CONTAINS A LIMIT 1")

Data with failed query

For example, Winter in California and Summer in californiaaaaa both match '.california.', and matched with the Sunny phrase.

The problem is that if the matched cell contains a single quote, it breaks the parsing of the query.

For unrelated reasons, I can't add columns to the table - it is being downloaded as a CSV by an external service and its format can't be changed.

What Have I Tried

  • REGEXREPLACE between the && that replaces the single quote with an empty string
  • Reviewing the manual
  • Using CONTAINS, which suffer from the same quoting problem

My Question

How can I escape single quotes in a cell content in the concat operator (&), in a query?

2 Answers 2

  • that's a common issue which can be outsmarted like this:

     "select Col2 
      where lower('"&SUBSTITUTE(A1, "'", "♦")&"') contains Col1 
      limit 1"))

     "select Col2 
      where lower('"&SUBSTITUTE(A1, "'", "♦")&"') contains Col1 
      limit 1"), "♦", "'"))



Plain simple quote "inversion":

=query('Book list'!A:F,"select B,C,D,E where F=""free"" and D="""&E1&"""")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.