Introducing the LIKE keyword in Azure Cosmos DB
You can now use the LIKE keyword to do text searches in Azure Cosmos DB SQL (core) API! The LIKE keyword has been a top Azure Cosmos DB feature request and many use cases will see tremendous value from new string search options in Azure Cosmos DB.
By including the LIKE keyword in a WHERE clause, you can search for specific string patterns. LIKE is a great addition to the existing string search system functions in Azure Cosmos DB:
|RegexMatch||Checks if a string contains a specific regular expression|
|Contains||Checks if a string contains a specific string value|
|StringEquals||Checks if a string equals a specific string value|
|StartsWith||Checks if a string starts with a specific string value|
|EndsWith||Checks if a string ends with a specific string value|
In this blog, we will run some sample queries using the LIKE keyword. All sample queries in this doc can be run against a dataset that is preloaded on the Azure Cosmos DB Query Playground.
Using LIKE, you can check if a string contains a specific pattern, including several different wildcard characters:
|%||Any string of zero or more characters|
|_ (underscore)||Any single character|
|[ ]||Any single character within the specified range ([a-f]) or set ([abcdef]).|
|[^]||Any single character not within the specified range ([^a-f]) or set ([^abcdef]).|
Here’s a simple example that uses LIKE with the % wildcard character to return all items with a description that contains “cereal”.
SELECT * FROM c WHERE c.description LIKE "%cereal%"
Here’s another example that uses the _ (underscore) to match a single character. This query will return all items with a description with a list that contains a section that starts with “Baby” and has any four letters and a comma afterwards.
SELECT * FROM c WHERE c.description LIKE "Baby____,%"
This example uses the [ ] wildcard to check if the description contains “Fruit pudding” or “fruit pudding”.
SELECT * FROM c WHERE c.description LIKE "%[Ff]ruit pudding%"
Using the ESCAPE clause:
You can search for patterns that include one or more wildcard characters using the ESCAPE clause. For example, if you wanted to search for descriptions that contained the string
20-30%, you wouldn’t want to interpret the
% as a wildcard character.
SELECT * FROM c WHERE c.description LIKE '%20-30!%%' ESCAPE '!'
You can write logically equivalent queries uses either the LIKE keyword or the RegexMatch system function. You’ll observe the same query performance regardless of which one you choose, so choose based on preference.