LIKE (Entity SQL)
Determines whether a specific character
String matches a specified pattern.
match [NOT] LIKE pattern [ESCAPE escape]
An Entity SQL expression that evaluates to a
A pattern to match to the specified
An escape character.
Specifies that the result of LIKE be negated.
true if the
string matches the pattern; otherwise,
Entity SQL expressions that use the LIKE operator are evaluated in much the same way as expressions that use equality as the filter criteria. However, Entity SQL expressions that use the LIKE operator can include both literals and wildcard characters.
The following table describes the syntax of the pattern
|_ (underscore)||Any single character.||
|[ ]||Any single character in the specified range ([a-f]) or set ([abcdef]).||
|[^]||Any single character not in the specified range ([^a-f]) or set ([^abcdef]).||
The Entity SQL LIKE operator and ESCAPE clause cannot be applied to
LIKE supports ASCII pattern matching and Unicode pattern matching. When all parameters are ASCII characters, ASCII pattern matching is performed. If one or more of the arguments are Unicode, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode with LIKE, trailing blanks are significant; however, for non-Unicode, trailing blanks are not significant. The pattern string syntax of Entity SQL is the same as that of Transact-SQL.
A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character
string. However, wildcard characters can be matched with arbitrary fragments of the character string. When it is used with wildcard characters, the LIKE operator is more flexible than the = and != string comparison operators.
You may use provider-specific extensions if you target a specific provider. However, such constructs may be treated differently by other providers, for example. SqlServer supports [first-last] and [^first-last] patterns where the former matches exactly one character between first and last, and the latter matches exactly one character that is not between first and last.
By using the ESCAPE clause, you can search for character strings that include one or more of the special wildcard characters described in the table in the previous section. For example, assume several documents include the literal "100%" in the title and you want to search for all of those documents. Because the percent (%) character is a wildcard character, you must escape it using the Entity SQL ESCAPE clause to successfully execute the search. The following is an example of this filter.
"title like '%100!%%' escape '!'"
In this search expression, the percent wildcard character (%) immediately following the exclamation point character (!) is treated as a literal, instead of as a wildcard character. You can use any character as an escape character except for the Entity SQL wildcard characters and the square bracket (
[ ]) characters. In the previous example, the exclamation point (!) character is the escape character.
The following two Entity SQL queries use the LIKE and ESCAPE operators to determine whether a specific character string matches a specified pattern. The first query searches for the
Name that starts with the characters
Down_. This query uses the ESCAPE option because the underscore (
_) is a wildcard character. Without specifying the ESCAPE option, the query would search for any
Name values that start with the word
Down followed by any single character other than the underscore character. The queries are based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:
Follow the procedure in How to: Execute a Query that Returns PrimitiveType Results.
Pass the following query as an argument to the
-- LIKE and ESCAPE -- If an AdventureWorksEntities.Products contained a Name -- with the value 'Down_Tube', the following query would find that -- value. Select value P.Name FROM AdventureWorksEntities.Products as P where P.Name LIKE 'DownA_%' ESCAPE 'A' -- LIKE Select value P.Name FROM AdventureWorksEntities.Products as P where P.Name like 'BB%'