Pattern matching in search conditions
The LIKE keyword searches for a string, date, or time value that matches a specified pattern. The LIKE keyword uses a regular expression to contain the pattern that the value will match. The pattern contains the string to search for, and the string can contain any combination of four wildcard characters.
Wildcard meaning
% contains any string of 0 or more characters.
_ any single character.
[] Specify any single character within a range (for example, [a-f]) or a collection (for example, [abcdef]).
[^] Any single character that is not in the specified range (for example, [^a-f]) or in a collection (for example, [^abcdef]).
Enclose wildcard characters and strings in single quotes, such as:
Like ' mc% ' will search for all strings (such as McBadden) that begin with the letter Mc.
Like '%inger ' searches for all strings (such as Ringer, Stringer) that end with the letter Inger.
Like '%en% ' will search all strings containing the letter en in any location (such as Bennet, Green, McBadden).
Like ' _heryl ' will search for all six-letter names (such as Cheryl, Sheryl) ending with the letter heryl.
Like ' [Ck]ars[eo]n ' will search for the following strings: Carsen, Karsen, Carson, and Karson (such as Carson).
Like ' [M-z]inger ' will search for all names (such as Ringer) that begin with a string inger ending with any single letter from M to Z.
Like ' m[^c]% ' will search for the beginning with the letter M, and the second letter is not all the names of C (such as Macfeather).
The following query finds all phone numbers in the authors table that have an area code of 415:
Select Phone
From Pubs.dbo.authors
Where phone like ' 415% '
You can use the same wildcard character with not. To find all phone numbers in the authors table for which the area code is not 415, use any of the following equivalence queries:
Copy Code code as follows:
Select Phone
From Pubs.dbo.authors
Where phone not like ' 415% '
--or
Select Phone
From Pubs.dbo.authors
Where not phone like ' 415% '
The is not NULL clause can be used in conjunction with wildcard characters and like clauses. For example, the following query retrieves all phone numbers that start with 415 and are not NULL from the authors table:
Copy Code code as follows:
Use pubs
Select Phone
From authors
Where phone like ' 415% ' and ' phone ' are not NULL
The output of important statements that contain the LIKE keyword depends on the sort order selected during the installation process.
The Where condition available for the text column is only like, is NULL, or PATINDEX.
Wildcards that are not used with like are interpreted as constants rather than patterns, in other words, they represent only their own values. The following query attempts to find a phone number consisting of only four characters 415%. The query does not look for a phone number that starts with 415.
Select Phone
From Pubs.dbo.authors
Where phone = ' 415% '
Another issue that should be considered when using wildcards is the impact on performance. An index cannot be used if the expression starts with a wildcard character. (just as given the name "%mith" rather than "Smith", you will not know which page of the phonebook to start looking.) The wildcard character at the middle or end of an expression does not interfere with indexing, as in the phone book, if the name is "samuel%", you should know where to start looking, regardless of whether Samuels and Samuelson are on the phone book.
Search wildcard characters
You can search for wildcard characters. There are two ways to specify characters that are commonly used as wildcards:
Use the Escape keyword to define an escape character. In a pattern, the wildcard is interpreted as a normal character when the escape character is placed before a wildcard. For example, to search for a string containing the string 5% anywhere, use:
Where ColumnA like '%5/%% ' ESCAPE '/'
In the LIKE clause above, the leading and trailing percent sign (%) is interpreted as a wildcard character, and the percent semicolon after the slash (/) is interpreted as% of characters.
Only the wildcard character itself is included in the square brackets ([]). To search for a dash (-) instead of specifying a search scope, specify the dash as the first character in square brackets:
Where ColumnA like ' 9[-]5 '
The following table shows the use of wildcard characters enclosed in square brackets.
Symbolic meaning
Like ' 5[%] ' 5%
Like ' 5% ' 5 followed by a string of 0 or more characters
Like ' [_]n ' _n
Like ' _n ' A, in, on
Like ' [A-CDF] ' A, B, C, D, or F
Like ' [-ACDF] '-, A, C, D, or F
Like ' [[] ' [
Like '] '
If you use like for string comparisons, all characters in the pattern string, including the starting and/or trailing spaces, are meaningful. If the query comparison requires that all rows containing "ABC" (with a space after ABC) are returned, the column value is not returned as "ABC" (No spaces after ABC) row. But conversely, this is not the case. You can ignore the trailing spaces in the expression you want the pattern to match. If the query comparison requires that all rows containing "ABC" (with no spaces after ABC) are returned, all rows starting with "ABC" with 0 or more trailing spaces will be returned.
