{"id":2339,"date":"2021-01-21T08:00:11","date_gmt":"2021-01-21T16:00:11","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=2339"},"modified":"2021-01-20T13:40:17","modified_gmt":"2021-01-20T21:40:17","slug":"like-keyword-cosmosdb","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/like-keyword-cosmosdb\/","title":{"rendered":"Introducing the LIKE keyword in Azure Cosmos DB"},"content":{"rendered":"

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<\/a> and many use cases will see tremendous value from new string search options in Azure Cosmos DB.<\/p>\n

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:<\/p>\n\n\n\n\n\n\n\n\n
System function<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
RegexMatch<\/td>\nChecks if a string contains a specific regular expression<\/td>\n<\/tr>\n
Contains<\/td>\nChecks if a string contains a specific string value<\/td>\n<\/tr>\n
StringEquals<\/td>\nChecks if a string equals a specific string value<\/td>\n<\/tr>\n
StartsWith<\/td>\nChecks if a string starts with a specific string value<\/td>\n<\/tr>\n
EndsWith<\/td>\nChecks if a string ends with a specific string value<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

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<\/a>.<\/p>\n

Using LIKE, you can check if a string contains a specific pattern, including several different wildcard characters:<\/p>\n\n\n\n\n\n\n\n
Wildcard character<\/td>\nDescription<\/td>\n<\/tr>\n
%<\/td>\nAny string of zero or more characters<\/td>\n<\/tr>\n
_ (underscore)<\/td>\nAny single character<\/td>\n<\/tr>\n
[ ]<\/td>\nAny single character within the specified range ([a-f]) or set ([abcdef]).<\/td>\n<\/tr>\n
[^]<\/td>\nAny single character not within the specified range ([^a-f]) or set ([^abcdef]).<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Examples:<\/span><\/h3>\n

Here\u2019s a simple example that uses LIKE with the % wildcard character to return all items with a description that contains \u201ccereal\u201d.<\/p>\n

Query:<\/strong><\/p>\n

SELECT\u00a0*\r\nFROM c\r\nWHERE c.description LIKE \"%cereal%\"\r\n\r\n<\/pre>\n
Here\u2019s 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 \u201cBaby\u201d and has any four letters and a comma afterwards.<\/p>\n
Query:<\/strong><\/p>\n
SELECT\u00a0*\r\nFROM c\r\nWHERE c.description LIKE \"Baby____,%\"<\/pre>\n
 <\/p>\n
This example uses the [ ] wildcard to check if the description contains \u201cFruit pudding\u201d or \u201cfruit pudding\u201d.<\/p>\n
Query:<\/strong><\/p>\n
SELECT\u00a0*\r\nFROM c\r\nWHERE c.description LIKE \"%[Ff]ruit pudding%\"\r\n\r\n<\/pre>\n
Using the ESCAPE clause:<\/span><\/p>\n
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\u00a020-30%<\/code>, you wouldn\u2019t want to interpret the\u00a0%<\/code>\u00a0as a wildcard character.<\/p>\n
Query:<\/strong><\/div>\n
<\/div>\n
SELECT *\r\nFROM c\r\nWHERE c.description LIKE '%20-30!%%' ESCAPE '!'<\/pre>\n
You can write logically equivalent queries uses either the LIKE keyword or the RegexMatch system function. You\u2019ll observe the same query performance regardless of which one you choose, so choose based on preference.<\/p>\n
Learn more:<\/strong><\/p>\n