The like wildcard character in SQL

Like
Determines whether the given string matches the specified pattern. Patterns can contain regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the string. However, you can use any fragment of a string to match a wildcard character. Using wildcard characters makes the LIKE operator more flexible than using the = and!= string comparison operators. If any argument is not a string data type, Microsoft®sql Server™ converts it to a string data type, if possible.
Grammar
Match_expression [NOT] like pattern [ESCAPE escape_character]
Parameters
Match_expression

A valid SQL Server expression for any string data type.

Pattern

The search pattern in Match_expression can contain the following valid SQL Server wildcard characters.
The wildcard Description example% contains any string of 0 or more characters. WHERE title like '%computer% ' will find all titles that contain the word computer anywhere in the title. _ (underline) any single character. WHERE au_fname like ' _ean ' will look for all 4-letter names (Dean, Sean, etc.) ending in EAN. [] Any single character in the specified range ([a-f]) or collection ([abcdef]). WHERE au_lname like ' [C-p]arsen ' will look for author surnames that begin with Arsen ending with any single character between C and P, for example, Carsen, Larsen, Karsen, and so on. [^] Any single character that does not belong to the specified range ([a-f]) or set ([abcdef]). WHERE au_lname like ' de[^l]% ' will look for the last names of all authors who started with de and whose letter is not followed by L.

Escape_character

Any valid SQL Server expression for all data types in the string data type Taxonomy. Escape_character has no default value and must contain only one character.
Result type
Boolean
Result value
If match_expression matches the specified pattern, like returns TRUE.
Comments
When using like for string comparisons, all characters in the pattern string are meaningful, including starting or trailing spaces. If the comparison in the query returns all rows that contain "ABC" (with a space after ABC), the row containing the column with "ABC" (with no spaces after ABC) will not be returned. However, you can ignore trailing spaces in the expression that the pattern will match. If the comparison in the query returns all rows that contain "ABC" (with no spaces after ABC), all rows starting with "ABC" with 0 or more trailing spaces will be returned.

Because of the way the data is stored, string comparisons that contain the char and varchar data schemas may not be compared through like. It is important to understand how each data type is stored and what causes like comparisons to fail. The following example passes a local char variable to a stored procedure and then uses pattern matching to find all the writings of an author. In this process, the author's last name is passed as a variable.

CREATE PROCEDURE find_books @AU_LNAME char asselect @AU_LNAME = RTRIM (@AU_LNAME) + '% ' SELECT t.title_id, T.title from a Uthors A, titleauthor ta, titles twhere a.au_id = ta.au_id and ta.title_id = t.title_id and a.au_lname like @AU_LNAME

When the number of characters contained in the name is less than 20 o'clock, the char variable (@AU_LNAME) will contain trailing spaces, which causes no rows to return during the find_books process. Because the au_lname column is varchar type, there are no trailing spaces. Because trailing spaces make sense, this process fails.

The following example is successful, however, because trailing spaces are not added to the varchar variable:

Use pubsgocreate PROCEDURE find_books2 @au_lname varchar (m) Asselect t.title_id, T.title from authors A, titleauthor TA, t Itles Twhere a.au_id = ta.au_id and ta.title_id = t.title_id and a.au_lname like @au_lname + '% ' EXEC find_books2 ' ring '

Here is the result set:

title_id title-----------------------------------------------------------------------MC3021 the gourmet microwave PS2091 is anger the enemy? PS2091 is anger the enemy? PS2106 Life Without Fear (4 row (s) affected)
Pattern matching using like
It is recommended to use like when searching for datetime values, because a datetime item may contain various date parts. For example, if you insert a value of 19981231 9:20 into a column named Arrival_time, the clause WHERE arrival_time = 9:20 will not be able to find an exact match for the 9:20 string because SQL Server converts it to 1900 years January 1 9:20. However, the clause WHERE arrival_time like '%9:20% ' will find a match.

Like supports ASCII pattern matching and Unicode pattern matching. ASCII pattern matching is performed when all parameters, including match_expression, pattern, and escape_character (if any) are ASCII character data types. If any of the parameters belong to a Unicode data type, all parameters are converted to Unicode and a Unicode pattern match is performed. Trailing spaces make sense when you use like for Unicode data (nchar or nvarchar data types). For non-Unicode data, however, trailing spaces are meaningless. Unicode like is compatible with the SQL-92 standard. ASCII like is compatible with earlier versions of SQL Server.

The following series of examples shows the differences between ASCII like pattern matching and the rows returned by the Unicode like pattern match:

--ASCII pattern matching with char columncreate TABLE t (col1 char ()) inserts into T VALUES (' Robert King ') SELECT * from T WHERE col1 like '% King '--returns 1 row--Unicode pattern matching with nchar columncreate TABLE T (col1 nchar ()) INS ERT into T VALUES (' Robert King ') SELECT * from T WHERE col1 like '% King '--no rows returned--Unicode pattern matching W ITH nchar column and rtrimcreate TABLE t (col1 nchar ()) INSERT into T VALUES (' Robert King ') SELECT * from T WHERE RTRIM ( col1) like '% King '--returns 1 row



Note If you use like for string comparisons, all characters in the pattern string are meaningful, including the starting or trailing spaces.


Using% wildcard characters
If you specify like ' 5% ', SQL Server searches for a number with 0 or more arbitrary characters followed by 5.

For example, this query displays all system tables in the database because they all start with the letter sys:

SELECT Table_namefrom information_schema. Tableswhere table_name like ' sys% '



Note: System tables can be changed with different versions. It is recommended that you use the Information Schema View or applicable stored procedures to process SQL Server system tables.



To refer to all objects in a system table, use not like ' sys% '. If you have 32 objects and like to find 13 names that match the pattern, then not will find 19 objects that do not match the like pattern.

Using the like ' [^s][^y][^s]% ' mode does not necessarily have the same name every time you find it. It is possible to get only 14 names (instead of 19), except for system table names, all names starting with s or the second letter Y or the third letter s are eliminated from the result. This is because matching a string with a reverse wildcard character is calculated in a step-by-step, one-time wildcard character. If any part of the calculation fails to match, it is eliminated.
Use wildcard characters as text
You can use a wildcard pattern matching string as a literal string by placing the wildcard characters in parentheses. The following table shows examples of using the LIKE keyword and [] wildcard characters.
Symbolic meaning like ' 5[%] ' 5%like ' [_]n ' _nlike ' [A-CDF] ' A, B, C, D or flike ' [-ACDF] '-, A, C, D or flike ' [[] ' [like '] ']like ' abc[_]d% ' abc_d and Abc_delike ' abc[def] ' abcd, ABCE and ABCF
Pattern matching using the ESCAPE clause
You can search for a string that contains one or more special wildcard characters. For example, a discounts table in the customers database might store a percent sign (%) The discount value. To search for a percent semicolon as a character instead of a wildcard, you must provide an escape keyword and an escape character. For example, a sample database contains a column named comment containing text 30%. To search for any row that contains string 30% anywhere in the comment column, specify a WHERE clause consisting of the where comment like '%30!%% '! '. If you do not specify escape and escape characters, SQL Server returns all rows containing the string 30.

The following example shows how to search for a string in the notes column of the Pubs database titles table "50% or more copies are purchased":

Use Pubsgoselect notesfrom titleswhere notes like "50%% off" or more copies are ' ESCAPE '% ' go
Example A. Using a like with% wildcard characters
The following example finds all phone numbers in the authors table that have an area code of 415.

Use Pubsgoselect phonefrom authorswhere phone as ' 415% ' ORDER by Au_lnamego

Here is the result set:

Phone------------415 658-9932 415 548-7723 415 836-7128 415 986-7020 415 836-7128 415 534-9219 415 585-4620 415 354-7128 415 834-2919 415 843-2991 415 935-4228 (one row (s) affected)
B. Using not like with% wildcard characters
The following example finds all phone numbers in the authors table that are not 415 in the area code.

Use Pubsgoselect phonefrom authorswhere phone does ' 415% ' ORDER by Au_lnamego

Here is the result set:

Phone------------503 745-6402 219 547-9982 615 996-8275 615 297-2723 707 938-6445 707 448-4982 408 286-2428 301 946-8853 801 826-0752 801 826-0752 913 843-0462 408 (s) 496-7223)
C. Use the ESCAPE clause
The following example uses the escape clause and the escape character to find the exact string 10-15% in the C1 column of the MYTBL2 table.

Use PUBSGOIF EXISTS (SELECT table_name from INFORMATION_SCHEMA. TABLES WHERE table_name = ' mytbl2 ') DROP table mytbl2gouse pubsgocreate table mytbl2 (c1 sysname) Goinsert VALUES (' Discount is 10-15% off ') INSERT mytbl2 VALUES (' Discount are. 10-.15 off ') Goselect C1 from Mytbl2where C1 like '%10-15!% off % ' ESCAPE '! ' Go
D. Using [] wildcard characters
The following example looks for an author whose name is Cheryl or Sheryl.

Use Pubsgoselect au_lname, au_fname, Phonefrom authorswhere au_fname "[Cs]heryl ' ORDER by au_lname ASC, au_fname ASCGO

The following example looks for the row of authors whose last name is Carson, Carsen, Karson, or Karsen.

Use Pubsgoselect au_lname, au_fname, Phonefrom authorswhere au_lname "[ck]ars[eo]n ' ORDER by au_lname ASC, au_fname as CGO

Please see


An expression

Function

SELECT

WHERE

©1988-2000 Microsoft Corporation. All rights reserved.