Like in SQL

Like

Determines whether the specified string matches the specified pattern. The mode can contain regular and wildcard characters. During pattern matching, regular characters must match exactly the characters specified in the string. However, any part of a string can be used to match a wildcard. And use = and! = Compared to string comparison operators, using wildcards makes the like operator more flexible. If no parameter belongs to the string data type, Microsoft SQL Server converts it to the string data type (if possible ).

Syntax

Match_expression[Not] LikePattern[EscapeEscape_character]

Parameters

Match_expression

A valid SQL Server expression for any string data type.

Pattern

Match_expressionCan contain the following valid SQL Server wildcard characters.

Wildcard	Description	Example
%	Any string containing zero or more characters.	Where title like '% Computer %' searches for all titles containing the word computer in any location of the title.
_ (Underline)	Any single character.	Where au_fname like '_ EAN' searches for the names of all four letters ending with EAN (such as dean and Sean ).
[]	Specifies any single character in the range ([a-f]) or set ([abcdef.	Where au_lname like '[C-P] Arsen' will look for the author's surname that ends with arsen and starts with any single character between C and P, such as Carsen, Larsen, Karsen, and so on.
[^]	It does not belong to any single character in the specified range ([a-f]) or set ([abcdef.	Where au_lname like 'de [^ L] %' searches for the last names of all authors whose names start with De and whose subsequent letters are not l.

Escape_character

Any valid SQL Server expression for all data types in string data type classification.Escape_characterNo Default Value and must contain only one character.

Result type

Boolean

Result Value

IfMatch_expressionMatches the specified mode, and like returns true.

Note

When using like for string comparison, All characters in the mode string are meaningful, including start or trailing spaces. If you want to return all rows that contain "ABC" (there is a space after ABC) for comparison in the query, the row of the column that contains "ABC" (no space after ABC) is not returned. However, trailing spaces in the expression to be matched by the pattern can be ignored. If you want to return all rows that contain "ABC" (no space after ABC) for comparison in the query, all rows starting with "ABC" with zero or multiple trailing spaces are returned.

For data storageChar AndVarchar Strings in the data mode may not be compared using the like method. It is important to understand the storage methods of each data type and the causes of like comparison failure. In the following exampleChar Variables are passed to the stored procedure, and then all the books of an author are searched using pattern matching. In this process, the author's surname is passed as a variable.

CREATE PROCEDURE find_books @AU_LNAME char(20)ASSELECT @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 in a name is less than 20,CharVariable (@Au_lname) will contain trailing spaces, which causesFind_booksNo rows are returned. BecauseAu_lnameColumnVarcharType, so there is no trailing space. This process fails because trailing spaces are meaningful.

However, the following example is successful because trailing spaces are not addedVarcharVariable:

USE pubsGOCREATE PROCEDURE find_books2 @au_lname varchar(20)ASSELECT 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 + '%'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)

Use like pattern matching

When searchingDatetimeWe recommend that you use like, becauseDatetimeItem may contain various date sections. For example, if you insert a value of 19981231 to the nameArrival_timeClause whereArrival_time= Will not be able to find the exact match of the string, because SQL Server converts it to AM, January 1, January 1, 1900. However, the clause whereArrival_timeLike '% 9:20%' will find a match.

Like supports ASCII and Unicode matching. When all parameters, includingMatch_expression,PatternAndEscape_characterIf all data types are ASCII characters, ASCII mode matching is performed. If any of these parameters belong to the Unicode data type, all parameters are converted to Unicode and matched in the Unicode mode. For Unicode data (NcharOrNvarcharData Type) When like is used, trailing spaces are meaningful. But for non-Unicode data, trailing spaces are meaningless. Unicode like is compatible with SQL-92 standards. ASCII like is compatible with earlier versions of SQL Server.

The following examples show the differences between the returned rows in the ASCII like mode match and the Unicode like mode match:

-- ASCII pattern matching with char columnCREATE 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 columnCREATE 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 RTRIMCREATE TABLE t (col1 nchar (30))INSERT INTO t VALUES ('Robert King')SELECT * FROM t WHERE RTRIM(col1) LIKE '% King'   -- returns 1 row

DescriptionIf you use like for string comparison, All characters in the mode string are meaningful, including Start spaces or trailing spaces.

Use % wildcard

If you specify like '5% ', SQL Server searches for the number 5 with zero or multiple arbitrary characters.

For example, this query will display all the system tables in the database, because they all start with the letter SYS:

SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE 'sys%'

DescriptionNote: The system tables can be changed with different versions. We recommend that you use the information architecture view or applicable stored procedures to process SQL Server System tables.

To view all objects in a non-system table, use not like 'sys % '. If there are 32 objects in total and 13 like matching pattern names, not like will find 19 objects that do not match the like pattern.

In the like '[^ s] [^ y] [^ s] %' mode, the names may not be the same each time. Only 14 names (not 19) may be obtained, except for the system table name, all names whose names start with s or the second letter is Y or the third letter is s will also be removed from the results. This is because matching strings with reverse wildcards is calculated step by step, with one wildcard at a time. If the matching fails in any part of the computing process, it will be eliminated.

Use wildcards as text

The wildcard pattern matching string can be used as a text string by placing the wildcard in parentheses. The following table shows examples of using the like keyword and the [] wildcard.

Symbol	Description
Like '5 [%]'	5%
Like '[_] N'	_ N
Like '[A-CdF]'	A, B, C, D, or F
Like '[-ACDF]'	-, A, C, D, or F
Like '[[]'	[
Like ']'	]
Like 'abc [_] d %'	Abc_d and abc_de
Like 'abc [DEF]'	ABCD, abce, and abcf

Use the escape clause pattern matching

You can search for strings that contain one or more special wildcards. For example,MERsIn the databaseDiscountsThe table may store discount values with a percent sign (%. To search for a percent sign as a character rather than a wildcard character, you must provide the escape keyword and escape character. For example, a sample database containsComment. The column contains 30% text. To searchCommentAny position in the column contains any row of the string 30%. specify where comment like '% 30! % 'Escape '! 'Where clause. If escape and escape characters are not specified, SQL server returns all rows containing string 30.

The following example describes howPubsDatabaseTitlesTableNotesSearch for the string "50% off when 100 or more copies are purchased" in the column ":

USE pubsGOSELECT notesFROM titlesWHERE notes LIKE '50%% off when 100 or more copies are purchased'    ESCAPE '%'GO

Example A. Use like with % wildcard

In the following example, we will find all the phone numbers in the authors table with a cell number of 415.

USE pubsGOSELECT phoneFROM authorsWHERE phone LIKE '415%'ORDER by au_lnameGO

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. Use Not like with % wildcard

Search in the following exampleAuthorsAll the telephone numbers with area numbers not 415 in the table.

USE pubsGOSELECT phoneFROM authorsWHERE phone NOT LIKE '415%'ORDER BY au_lnameGO

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. Use the Escape Clause

In the following example, use the escape clause and escape character to searchMytbl2TableC1The exact string 10-15% in the column.

USE pubsGOIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES      WHERE TABLE_NAME = 'mytbl2')   DROP TABLE mytbl2GOUSE pubsGOCREATE TABLE mytbl2( c1 sysname)GOINSERT mytbl2 VALUES ('Discount is 10-15% off')INSERT mytbl2 VALUES ('Discount is .10-.15 off')GOSELECT c1 FROM mytbl2WHERE c1 LIKE '%10-15!% off%' ESCAPE '!'GO

D. Use the [] wildcard

In the following example, find the author whose name is Cheryl or Sheryl.

USE pubsGOSELECT au_lname, au_fname, phoneFROM authorsWHERE au_fname LIKE '[CS]heryl'ORDER BY au_lname ASC, au_fname ASCGO

In the following example, find the row of the author Carson, Carsen, karson, or Karsen.

USE pubsGOSELECT au_lname, au_fname, phoneFROM authorsWHERE au_lname LIKE '[CK]ars[eo]n'ORDER BY au_lname ASC, au_fname ASCGO