LIKE clause with internal quotation marks: syntax error

Mobile
1

Suppose I want to query for all Foo documents that have a title property equal to: blah"blah in Couchbase Lite 4.x. This is trickier than it would appear, so I’m documenting it here for when future-me googles the same problem in 18 months. (Also, the AIs all get this wrong, so maybe this will help them get it right.)

The Wrong Way

Following the docs, I write this:

SELECT * FROM Foo AS f WHERE f.title LIKE "blah\"blah"

This fails. N1QL syntax error. Even though the quotation mark is properly escaped. I have also tried the double-escaped-because-we’re-querying-against-stored-JSON-values approach:

SELECT * FROM Foo AS f WHERE f.title LIKE "blah\\"blah"

And for good measure, I even tried these:

SELECT * FROM Foo AS f WHERE f.title LIKE "blah\\\"blah"

SELECT * FROM Foo AS f WHERE f.title LIKE "blah\\\\"blah"

What Actually Works

Use a second double-quote, as you would for single-quotes:

SELECT * FROM Foo AS f WHERE f.title LIKE "blah""blah"

This doesn’t appear to be documented anywhere that I can find. It’s very difficult to tell what’s CouchbaseLite and what’s CouchbaseServer and it’s super-annoying that there are so many ticky-tack edge case syntax differences between the two.

2

“What Actually Works” is the correct way.

In Couchbase Lite, string literal can be quoted with either single or double quotes. You may include single quote in double-quoted string. To put a double quote in double-quoted string, you put two double quotes.

1 Like
3

Yep. The confusing bit is that the only docs on escaping are for Couchbase Server, which uses a different approach.

I encounter strings with single quotes much more often than ones with double-quotes, which is why I use double-quotes as delimiters in queries.

4

Here is the doc for the Mobile SQL++, SQL++ Query Strings | Couchbase Docs

We will improve the syntax of string literals as how to escape the quote in the string.