Google-sheets – Escape single quote in cell content in a QUERY

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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?

Best Answer

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

    =ARRAYFORMULA(QUERY(SUBSTITUTE(Regex!A:B, "'", "♦"), 
 "select Col2 
  where lower('"&SUBSTITUTE(A1, "'", "♦")&"') contains Col1 
  limit 1"))

    =ARRAYFORMULA(SUBSTITUTE(QUERY(SUBSTITUTE(Regex!A:B, "'", "♦"), 
 "select Col2 
  where lower('"&SUBSTITUTE(A1, "'", "♦")&"') contains Col1 
  limit 1"), "♦", "'"))

0

Related Topic