Introducing the LIKE keyword in Azure Cosmos DB

Tim

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:

System function Description
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:

Wildcard character Description
% 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]).

 

Examples:

Here’s a simple example that uses LIKE with the % wildcard character to return all items with a description that contains “cereal”.

Query:

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.

Query:

SELECT *
FROM c
WHERE c.description LIKE "Baby____,%"

 

This example uses the [ ] wildcard to check if the description contains “Fruit pudding” or “fruit pudding”.

Query:

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.

Query:
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.

Learn more:

 

 

 

 

2 comments

Comments are closed. Login to edit/delete your existing comments

  • Kane Barton

    Wondering if there is a difference in RUs consumed between LIKE and CONTAINS statements (for the first example)?

    • Krzysztof Madej

      I made small tests:

      – in queries with partition key it was 5.13 RU versus 5.12 RU (like versus contains)
      – in queries without partition key 23.75 RU versus 24 RU (like versus contains)

      So differences, are really minimal.