How to write a LIKE query in Azure CosmosDB?

Question

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.

Answer 1

This can be achieved in 2 ways

(i) Currently Azure Cosmosdb supports the CONTAINS, STARTSWITH, and ENDSWITH built-in functions which are equivalent to LIKE.

The keyword for LIKE in Cosmosdb is Contains .

SELECT * FROM c WHERE CONTAINS(c.pi, '09')

So, in your case if you want to match the pattern 09%001, you need to use:

SELECT * FROM c WHERE STARTSWITH(c.pi, '09') AND ENDSWITH(c.pi, '001')

(ii) As 404 mentioned, Use SQL API User Defined Functions which supports regex :

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

SELECT udf.EXECUTE_REGEX("foobar", ".*bar")