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

UPDATE :

You can now use the LIKE keyword to do text searches in Azure Cosmos DB SQL (core) API!

EXAMPLE:

SELECT * FROM c WHERE c.description LIKE "%cereal%" 

OLD Answer:

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")