Like
Determine if a given string matches the specified pattern. The mode can contain regular characters and wildcard characters. During the pattern matching process, the regular characters must be fully matched with the characters specified in the string. However, the wildcard can be matched using any fragment of the string. Compared with the operator compare the operator using = and! = String, using wildcards can make the LIKE operator more flexible. If any parameters do not belong to a string data type, Microsoft® SQL ServerTM converts it into a string data type (if possible).
grammar
Match_expression [not] Like Pattern [escape escape_character]
parameter
Match_expression
Valid SQL Server expressions for any string data type.
Pattern
The search mode in match_expression can contain the following valid SQL Server wildcards.
Wildcard Description Example% contains any string of zero or more characters. Where title Like '% computer%' will find all the book names that contain the word computer in any of the book name. _ (Underline) any single character. WHERE AU_FNAME LIKE '_ean' will look for all 4 letters ending with EAN (Dean, Sean, etc.). [] Specify the range ([A-F]) or any single character in the collection ([ABCDEF]). WHERE AU_LNAME LIKE '[C-P] Arsen' will look up with ARSEN's last name, for example, Carsen, Larsen, Karsen, etc. at the end of arsen and any single character between C and P. [^] Does not belong to any individual characters of the specified range ([A-F]) or a collection ([Abcdef]). WHERE AU_LNAME LIKE 'DE [^ l]%' will look for all the authors of DE and after the letters of L.
Escape_character
Any valid SQL Server expressions for all data types in the string data type class. Escape_character has no default value and must only contain one character.
Type
Boolean
Result value
If the match_expression matchs the specified mode, Like will return TRUE.
Comment
When using Like for string comparison, all characters in the mode string make sense, including starting or tail space. If the comparison in the query returns all rows containing "ABC" (ABC has a space), the column that contains "ABC" (there is no space after ABC) will not return. However, you can ignore the trailing spaces in the expression you want to match. If the comparison in the query is to return all rows containing "ABC" (there is no space after ABC), it will return all rows that start with "ABC" and have zero or more endless spaces.
Due to data storage methods, comparison with a string containing CHAR and VARCHAR data modes may not be compared to the LIKE. It is important to learn about the storage method of each data type and cause the LIKE comparison failure. The following example passes the partial char variable to the stored procedure, then use the pattern match to find all the authors' works. In this process, the author's last name will be transmitted as a variable.
Create Procedure Find_books @au_lname char (20)
AS
SELECT @AU_LNAME = RTRIM (@au_lname) '%'
SELECT T.TITLE_ID, T.TITLE
From Authors 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, the char variable (@au_lname) will contain end-bearing spaces, which causes no rows to return during the Find_Books. Since the AU_LNAME is listed as a varchar type, there is no trailing space. Because the trailing space is meaningful, this process failed.
However, the following example is successful because the trailing space is not added to the VARCHAR variable:
USE PUBS
Go
Create Procedure Find_books2 @au_lname varchar (20)
AS
SELECT T.TITLE_ID, T.TITLE
From Authors A, Titleauthor Ta, Titles T
Where a.au_id = ta.au_id and ta.title_id = T.TITLE_ID
And A.au_lname like @au_lname '%'
EXEC FIND_BOOKS2 'RING'
The following 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)
Match using the pattern of LIKE
When searching for a DateTime value, it is recommended to use Like because the DateTime item may contain various date sections. For example, if the value 19981231 9:20 is inserted into a column name arral_time, the clause where arrAl_time = 9:20 will not find the precise match of the 9:20 string, because SQL Server converts it to January 1900 1st at 9:20 am. However, the clause where arrival_time like '% 9: 20%' will find a match.
Like supports ASCII mode matching and Unicode mode matching. When all parameters, including match_expression, pattern, and escape_character are all ASCII character data types, the ASCII mode match is executed. If any parameters belong to the Unicode data type, all parameters will be converted to Unicode and perform Unicode mode matching. When using Like with Like for Unicode data (Nchar or NVARCHAR data type), the trailing space is meaningful. But for non-Unicode data, there is no significant difference between trailing spaces. Unicode Like is compatible with SQL-92 standards. Ascii Like is compatible with the earlier versions of SQL Server.
A series of examples showing the difference between the ASCII Like mode matching the row returned by the Unicode Like mode:
- Ascii Pattern matching with char column
Create Table T (Col1 Char (30))
INSERT INTO T VALUES ('Robert King') SELECT *
From t
WHERE col1 like '% king' - returns 1 row
- Unicode Pattern matching with nchar column
Create Table T (Col1 nchar (30))
INSERT INTO T VALUES ('Robert King')
SELECT *
From t
WHERE col1 like '% king' - no rows returned
- Unicode Pattern matching with nchar column and rtrim
Create Table T (Col1 nchar (30))
INSERT INTO T VALUES ('Robert King')
SELECT *
From t
WHERE RTRIM (COL1) LIKE '% KING' - RETURNS 1 ROW
Note If you use Like to make a string comparison, all characters in the mode string make sense, including start spaces or trailing spaces.
Use% wildcard
If you specify Like '5%', SQL Server will search for numbers 5 with zero or more characters later.
For example, this query will display all system tables in the database because they start with the letter SYS:
SELECT TABLE_NAME
From information_schema.taables
Where Table_name Like 'Sys%'
Notes Please note: The system table can be changed with the version. It is recommended to use the information architectural view or the applicable stored procedure to handle the SQL Server system table.
To see all objects of non-system tables, use Not Like 'Sys%'. If a total of 32 objects and Like find 13 names that match the mode match, NOT LIKE will find the object that does not match the LIKE mode.
Use Like '[^ s] [^ y] [^ s]%' mode not necessarily the name of each found. May only get 14 names (rather than 19), in addition to the system table name, all names that start or second letters as Y or the third letter will be eliminated from the results. This is because the inverse wildcard match string is a string step for calculation, one wildcard. If you match any link during the calculation process, then it will be eliminated.
Use wildcard as a text
You can match the wildcard mode to the string as a text string, and the method is to place wildcards in parentheses. The table below shows an example of using the LIKE keyword and [] wildcard.
Symbolic meaning Like '5 [%]' 5% like '[_] n'_nlike' [A-cdf] 'A, B, C, D or FLIKE' [-ACDF] '-, A, C, D or FLIKE '[]' [Like ']'] ABC_D and ABC_DELIKE 'ABC [DEF]' Abcd, ABCE and ABCF
Matching the pattern of the Escape clause
A string containing one or more special wildcards can be searched. For example, the discounts table in the Customers database may store discount values containing percent (%). To search for a percent sign as a character rather than a wildcard, you must provide an Escape keyword and an escape. For example, a sample database contains columns named Comment, which contains 30% of text. To search any of any of the positions in the Comment column contain any rows of strings, specify the WHERE clause consisting of where comment like '% 30!'. 'Escape'! '. If you do not specify an Escape and an escape, SQL Server will return all rows of string 30. The following example shows how to search for strings in the Notes column of the Pubs Database Titles table "50% Off When 100 or more Copies Are Purchased":
USE PUBS
Go
SELECT Notes
From titles
Where notes like '50 %% Off When 100 or more Copies Are Purchased '
ESCAPE '%'
Go
Example
A. Using LIKE with% wildcard
The following example finds a telephone number of all the area code 415 in the Authors table.
USE PUBS
Go
SELECT Phone
From authors
WHERE Phone Like '415%'
ORDER by au_lname
Go
The following 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
(11 row (s) affected)
B. NOT LIK with% wildcard
The following example looks out that all the area code in the Authors table is not 415 phone number.
USE PUBS
Go
SELECT Phone
From authors
WHERE Phone Not Like '415%'
ORDER by au_lname
Go
The following 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 496-7223
(12 row (s) affected)
C. Using the Escape clause
The following example uses the Escape clause and the escape symbol to find 10-15% of the exact string in the C1 column of the MyTBL2 table.
USE PUBS
Go
IF exists (SELECT TABLE_NAME from Information_Schema.Tables
Where table_name = 'myTBL2')
DROP TABLE MYTBL2
Go
USE PUBS
Go
Create Table MyTBL2
(
C1 sysname
)
Go
INSERT MyTBL2 VALUES ('Discount IS 10-15% Off ")
INSERT MyTBL2 VALUES ('discount is .10-.15 OFF')
Go
SELECT C1
From mytbl2
WHERE C1 Like '% 10-15!% OFF%' Escape '!' Go
D. Using [] wildcard
The following example looks out the name of CHERYL or SHERYL.
USE PUBS
Go
SELECT AU_LNAME, AU_FNAME, PHONE
From authors
WHERE AU_FNAME LIKE '[CS] HERYL'
Order by au_lname ASC, AU_FNAME ASC
Go
The following example shows the row of the author of Carson, Carsen, Karson or Karsen.
USE PUBS
Go
SELECT AU_LNAME, AU_FNAME, PHONE
From authors
WHERE AU_LNAME LIKE '[CK] ARS [EO] N'
Order by au_lname ASC, AU_FNAME ASC
Go
See
Expression function Select Where
© 1988-2000 Microsoft Corporation. all rights reserved.
