Wildcard Characters
Function
Represent one or more characters, or a range of characters, in a match string.Using Wildcard Characters
Use wildcard characters in where and having clauses to find character or date/time information that is like¾or not like¾the match string:{where | having} [not]
expression [not] like match_string [escape "escape_character"]
expression can be any combination of column names, constants, or functions with a character value.match_string is the pattern to be found in the expression. It can be a any combination of constants, variables, and column names or a concatenated expression, such as:
like @variable + "%".If the match string is a constant, it must always be enclosed in single or double quotes.
The Percent (%) Wildcard Character
Use the % wildcard character to represent any string of zero or more characters. For example, to find all the phone numbers in the authors table that begin with the 415 area code:select phone from authors where phone like "415%"To find names that have the characters "en" in them (Bennet, Green, McBadden):
select au_lname from authors where au_lname like "%en%"Trailing blanks following "%" in a like clause are truncated to a single trailing blank. For example, "%" followed by 2 spaces matches
"X "(one space); "X " (two spaces); "X " (three spaces), or any number of trailing spaces.
The Underscore (_) Wildcard Character
Use the _ wildcard character to represent any single character. For example, to find all six-letter names that end with "heryl" (for example, Cheryl):select au_fname from authors where au_fname like "_heryl"
Bracketed ([ ]) Characters
Use brackets to enclose a range of characters, such as [a-f], or a set of characters such as [a2Br]. When ranges are used, all values in the sort order between (and including) rangespec1 and rangespec2 are returned. For example, "[0-z" matches 0-9, A-Z and a-z (and several punctuation characters) in 7-bit ASCII.To find names ending with "inger" and beginning with any single character between M and Z:
select au_lname from authors where au_lname like "[M-Z]inger"To find both "DeFrance" and "deFrance":
select au_lname from authors where au_lname like "[dD]eFrance"
The Caret (^) Wildcard Character
The caret is the negative wildcard character. Use it to find strings that do not match a particular pattern. For example, "[^a-f]" finds strings that are not in the range a-f and "[^a2bR]" finds strings that are not "a," "2," "b," or "R."To find names beginning with "M" where the second letter is not "c":
select au_lname from authors where au_lname like "M[^c]%"When ranges are used, all values in the sort order between (and including) rangespec1 and rangespec2 are returned. "[0-z]" , for example, matches 0-9, A-Z , a-z, and several punctuation characters in 7-bit ASCII.
Using not like
Use not like to find strings that do not match a particular pattern. These two queries are equivalent: they find all the phone numbers in the authors table that do not begin with the 415 area code.select phone from authors where phone not like "415%" select phone from authors where not phone like "415%"
not like and ^ May Give Different Results
You cannot always duplicate not like patterns with like and the negative wildcard character [^]. This is because not like finds the items that do not match the entire like pattern, but like with negative wildcard characters is evaluated one character at a time.For example, this query finds the system tables in a database whose names begin with "sys":
select name from sysobjects where name like "sys%"To see all the objects that are not system tables, use
not like "sys%"If you have a total of 32 objects and like finds 13 names that match the pattern, not like will find exactly the 19 objects that do not match the pattern.
A pattern such as like "[^s][^y][^s]%" may not produce the same results. Instead of 19, you might get only 14, with all the names that begin with "s" or have "y" as the second letter or have "s" as the third letter eliminated from the results, as well as the system table names. This is because match strings with negative wildcard characters are evaluated in steps, one character at a time. If the match fails at any point in the evaluation, it is eliminated.
Case and Accent Insensitivity
If your SQL Server uses a case-insensitive sort order, case is ignored when comparing expression and match_string. For example, this clause:where col_name like "Sm%"would return "Smith," "smith," and "SMITH" on a case-insensitive SQL Server.
If your SQL Server is also accent-insensitive, it will treat all accented characters as equal to each other and to their unaccented counterparts, both uppercase and lowercase. The sp_helpsort system procedure displays the characters that are treated as equivalent, displaying an "=" between them.
Using Multibyte Wildcard Characters
If the multibyte character set configured on your SQL Server defines equivalent double-byte characters for the wildcard characters _, %, - [, ], and ^, you can substitute the equivalent character in the match string. The underscore equivalent represents either a single- or double-byte character in the match string.Using Wildcard Characters As Literal Characters
To search for the occurrence of a %, _, [, ], or ^ within a string, you must use an escape character. When a wildcard character is used in conjunction with an escape character, SQL Server interprets the wildcard character literally, rather than using it to represent other characters.SQL Server provides two types of escape characters:
- Square brackets (a Transact-SQL extension)
- Any single character that immediately follows an escape clause (compliant with the SQL standards)
Using Square Brackets As Escape Characters
Use square brackets as escape characters for the percent sign, the underscore, and the open bracket. The close bracket does not need an escape character; use it by itself. If you use the dash as a literal character, it must be the first character inside a set of square brackets.Table 5-26 shows some examples of square brackets as escape characters:
| like Predicate | Meaning |
|---|---|
| like "5%" | 5 followed by any string of 0 or more characters |
| like "5[%]" | 5% |
| like "_n" | an, in, on (and so on) |
| like "[_]n" | _n |
| like "[a-cdf]" | a, b, c, d, or f |
| like "[-acdf]" | -, a, c, d, or f |
| like "[[ ]" | [ |
| like "]" | ] |
| like "[[]ab]" | []ab |
Using the escape Clause
Use the escape clause to specify an escape character. Any single character in the server's default character set can be used as an escape character. If you try to use more than one character as an escape character, SQL Server generates an exception.Do not use existing wildcard characters as escape characters because:
- If you specify the underscore ( _ ) or percent sign (%) as an escape character, it loses its special meaning within that like predicate, and acts only as an escape character.
- If you specify the opening or closing bracket ( [ or ] ) as an escape character, the Transact-SQL meaning of the bracket is disabled within that like predicate.
- If you specify the hyphen or caret (- or ^) as an escape character, it loses its special meaning and acts only as an escape character.
The escape character is valid only within its like predicate and has no effect on other like predicates contained in the same statement. The only characters that are valid following an escape character are the wildcard characters ( _, %, [, ], or [^] ), and the escape character itself. The escape character affects only the character following it, and subsequent characters are not affected by it.
If the pattern contains two literal occurrences of the character that happens to be the escape character, the string must contain four consecutive escape characters. If the escape character does not divide the pattern into pieces of one or two characters, SQL Server returns an error message.
Following are examples of like predicates with escape clauses:.
| like Predicate | Meaning |
|---|---|
| like "5@%" escape "@" | 5% |
| like "*_n" escape "*" | _n |
| like "%80@%%" escape "@" | String containing 80% |
| like "*_sql**%" escape "*" | String containing _sql* |
| like "%#####_#%%" escape "#" | String containing ##_% |
To enforce standard behavior and disable the special meaning of the square brackets, use set fipsflagger on.
Wildcard Characters Have No Special Meaning Without like
Wildcard characters used without like have no special meaning. For example, this query finds any phone numbers that start with the four characters "415%":select phone from authors where phone = "415%"
Using Wildcard Characters with datetime Data
When you use like with datetime values, SQL Server converts the dates to the standard datetime format, and then to varchar. Since the standard storage format does not include seconds or milliseconds, you cannot search for seconds or milliseconds with like and a pattern.It is a good idea to use like when you search for datetime values, since datetime entries may contain a variety of date parts. For example, if you insert the value "9:20" and the current date into a column named arrival_time, the clause:
where arrival_time = '9:20'would not find the value, because SQL Server converts the entry into "Jan 1 1900 9:20AM." However, the clause below would find this value:
where arrival_time like '%9:20%'
