Pattern Matching in search criteria

The like keyword searches for the string, date, or time value that matches the specified pattern. For more information, see data types. The like keyword uses the regular expression to include the pattern to be matched by the value. The mode contains the string to be searched. A string can contain any combination of four wildcard characters.

Wildcard	Description
%	Any string containing zero or more characters.
_	Any single character.
[]	Specifies any single character in the range (for example, [a-f]) or set (for example, [abcdef.
[^]	Any single character that is not in the specified range (for example, [^ a-f]) or set (for example, [^ abcdef.

Use single quotes to enclose wildcards and strings. For example:

• Like 'mc % 'searches all strings starting with MC (for example, mcbadden ).

• Like '% Inger' searches all strings ending with the letter Inger (such as ringer and Stringer ).
• Like '% en %' searches all strings (such as Bennet, green, and mcbadden) that contain letters en at any position ).
• Like '_ heryl' searches for names of all six letters ending with heryl, such as Cheryl and Sheryl ).
• Like '[Ck] ARS [EO] n' will search for the following strings: Carsen, Karsen, Carson, and karson (such as Carson ).
• Like '[M-Z] Inger' searches for all names (such as ringer) ending with string Inger, starting with any single letter from m to Z ).
• Like'm [^ C] %' searches for all names (such as macfeather) whose names start with M and whose second letter is not C ).

The following queryAuthorsIn the table, find all the telephone numbers with area numbers 415:

 
Select phonefrom pubs. DBO. authorswhere phone like '20140901'
 

Not like can be used with the same wildcard. ToAuthorsThe following table lists all phone numbers whose Partition Number is not 415. use any of the following equivalent queries:

 
Select phonefrom pubs. DBO. authorswhere phone not like '000000' -- orselect phonefrom pubs. DBO. authorswhere not phone like '000000'
 

The is not null clause can be used with wildcards and like clauses. For exampleAuthorsRetrieve all phone numbers starting with 415 and is not null in the table:

 
Use pubsselect phonefrom authorswhere phone like '000000' and phone is not null
 

ImportantThe output result of a statement containing the like keyword depends on the sorting order selected during installation. For more information about the impact of different sorting orders, see sorting rules.

 

AvailableTextThe where condition of a column is like, is null, or patindex.

Wildcards that are not used with like are interpreted as constants rather than schemas. In other words, these wildcards only represent their own values. The following query attempts to find a phone number that consists of only four characters, 415%. This query does not look for phone numbers starting with 415. For more information about constants, see use constants.

 
Select phonefrom pubs. DBO. authorswhere phone = '000000'
 

Another issue that should be taken into consideration when using wildcards is the impact on performance. If the expression starts with a wildcard, the index cannot be used. (Given the name "% mith" instead of "Smith", you cannot find the page from the phone book .) Wildcards in the middle or end of an expression do not affect the use of indexes. Just like in a phone book, if the name is "Samuel %", whether or not both of them are in the phone book, you should know where to start searching.

Search for wildcard characters

You can search for wildcard characters. There are two ways to specify characters that are typically used as wildcards:

• Use the escape keyword to define the escape character. In mode, when an escape character is placed before a wildcard, the wildcard is interpreted as a common character. For example, to search for strings containing string 5% at any position, use:

  Where Columna like '% 5/%' escape '/'

  In the like clause above, the leading and ending percent signs (%) are interpreted as wildcards, while the percent signs after the slash (/) are interpreted as characters %.

• Square brackets ([]) contain only the wildcard character. To search for a break number (-) instead of specifying the search range, specify the break number as the first character in square brackets:

   
  Where Columna like '9 [-] 5'
   

  The following table shows the usage of wildcards enclosed in square brackets.

  Symbol	Description
  Like '5 [%]'	5%
  Like '5%'	5 followed by a string of 0 or more characters
  Like '[_] N'	_ N
  Like '_ N'	An, in, on (and so 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 comparison, all the characters in the mode string, including the start space and/or trailing space, are meaningful. If the query comparison requires that all rows containing "ABC" (there is a space after ABC) be returned, the column value "ABC" (no space after ABC) is not returned. However, this is not the case. Ignore spaces at the end of the expression to be matched by the pattern. If the query comparison requires that all rows containing "ABC" (without spaces after ABC) be returned, all rows starting with "ABC" with zero or multiple trailing spaces are returned.