Escaping wildcards in LIKE

Couchbase Server
1

This query works great:

SELECT * FROM Test AS x WHERE x.type='item' AND (LOWER(x.`name`) LIKE 'a%')

In my use case, the user basically just asks for items whose name starts with “a”. Works great, but if the item happens to have a name with “_” or “%” in it and I want to match those characters, I’m out of luck.

The docs say that I can escape these two wildcards with backslashes. But whenever I do so, it just returns everything.

SELECT * FROM Test AS x WHERE x.type='item' AND (LOWER(x.`name`) LIKE 'new\_a%')

Same with both “_” and “%”.

Index definition:

CREATE INDEX `TestItems` ON `Test`(`type`,`name`) WHERE (`type`="item")

This happens in 4.5.1.
In 4.6 preview, it does the same, but one difference is if I remove the LOWER(..), it just returns nothing instead of everything.

2

It turns out you have to doubly escape them.
Bear with me and I’ll make it better.

cbq> insert into default (key, value) values("1", {"a": "_a"});
{
    "requestID": "9c8bb82a-1865-48b4-b03a-a2592875f167",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.502586ms",
        "executionTime": "1.473251ms",
        "resultCount": 0,
        "resultSize": 0,
        "mutationCount": 1
    }
}
cbq> insert into default (key, value) values("2", {"a": "aa"});
{
    "requestID": "41b9130f-cdd4-4044-91e3-8193d8299c67",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.353028ms",
        "executionTime": "1.322679ms",
        "resultCount": 0,
        "resultSize": 0,
        "mutationCount": 1
    }
}
cbq> select * from default where a like "\_a%";
{
    "requestID": "043dc666-8bba-4396-b00e-508041e7da54",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "default": {
                "a": "_a"
            }
        },
        {
            "default": {
                "a": "aa"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "8.375002ms",
        "executionTime": "8.353933ms",
        "resultCount": 2,
        "resultSize": 152
    }
}
cbq> select * from default where a like "\\_a%";
{
    "requestID": "4f9033b1-76cd-4b35-859f-1a07ec59c946",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "default": {
                "a": "_a"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "10.322298ms",
        "executionTime": "10.296736ms",
        "resultCount": 1,
        "resultSize": 76
    }
}
cbq> insert into default (key, value) values("3", {"a": "%a"});
{
    "requestID": "b7bb2b2b-6348-4466-9bb9-a9aa9dd1cf6a",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.051446ms",
        "executionTime": "1.025346ms",
        "resultCount": 0,
        "resultSize": 0,
        "mutationCount": 1
    }
}
cbq> select * from default where a like "\\%a%";
{
    "requestID": "96c6e3c9-599e-40c0-9685-925045ea979d",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "default": {
                "a": "%a"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.604913ms",
        "executionTime": "9.580158ms",
        "resultCount": 1,
        "resultSize": 76
    }
}
1 Like
3

Hi @marcog, please format the answer as preformatted text.

4

Okay, so we just have to double escape the LIKE wildcards.

'_' => '\\_'
'%' => '\\%'

Notably, single and double quotes have same behavior here.
Works great, thanks!

1 Like