Escaping wildcards in LIKE

Couchbase Server
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