[SOLVED] How to write a LIKE query in Azure CosmosDB?

How to write a LIKE query in Azure CosmosDB?

I want to retrieve data from Cosmos DB with the following query:

SELECT * FROM c WHERE c.pi like '09%001'

(This is a SQL query, which I can use in MySQL)

Here, pi is a string value, which can be 09001001 or 09025001.

Is there a way to use a LIKE command in Cosmos DB?

I know that cosmos DB uses CONTAINS, but this cannot be used when you want to match specifically the beginning or end of the string.

Solution

Another possibility is creating your own User Defined Function. As example here's a regex check:

function matchRegex(str, pattern) {
    let regex=RegExp(pattern);
    return regex.test(str);
}

Created under the name MATCH_REGEX it can then be used like:

SELECT udf.MATCH_REGEX("09001001", "^09.*001$")

As note: it'll kill any index optimization that for instance STARTSWITH would have. Although allows for much more complex patterns. It can therefor be beneficial to use additional filters that are capable of using the index to narrow down the search. E.g. using StartsWith(c.property1, '09') as addition to the above example in a WHERE clause.

UPDATE:

Cosmos now has a RegexMatch function that can do the same. While the documentation for the MongoApi mentions the $regex can use the index to optimize your query if it adheres to certain rules this does not seem to be the case for the SqlApi (at this moment).

UPDATE:

The RegexMatch function can leverage the range index now for patterns that check the start or end of string for specific characters.