LIKE predicate

Syntax:

LIKE::=
< expression for calculating the string value>
[NOT] LIKE < expression for calculating the string value>
[ESCAPE < character>]

The LIKE predicate compares the string specified in the first expression for calculating the string value, which is refered to as a value to test, with the pattern that is defined in the second expression for calculating the string value. The pattern may contain any combination of wildcards such as:

  • Underline symbol (_), which can be used instead of any single character in the value to test;
  • Percent sign (%), which replaces any string of zero or more characters in the value to test.

If the value to test matches the pattern with the wildcards, the preducate equals TRUE. Below are some examples of the patterns.

PatternDescription
‘abc%’Any rows that begin with the characters “abc”.
‘abc_’The rows with the length of strictly 4 characters, the first characters of the string being “abc”.
‘%z’Any character sequence that necessarily ends with the character “z”.
‘%Rostov%’Any character sequence including the word “Rostov” at any position of the string.

Example 5.4.1

Find all the ships the class names of those end with the character ‘о’:
SELECT *
FROM Ships
WHERE class LIKE '%o';
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

The result of that query is the following table:

nameclasslaunched
HarunaKongo1916
HieiKongo1914
KirishimaKongo1915
KongoKongo1913
MusashiYamato1942
YamatoYamato1941

Example 5.4.2

Find all the ships the class names ending with the character ‘о’ but not with the characters ‘go’:
SELECT *
FROM Ships
WHERE class NOT LIKE '%go' AND
class LIKE '%o';
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
nameclasslaunched
MusashiYamato1942
YamatoYamato1941

If the string to search includes a wildcard as a character, then specify a control symbol in the ESCAPE clause. This control symbol is to be used in the pattern prior to the wildcard to warn that the wildcard here should be considered as an ordinary character. For example, if the value to search includes the character “”, the pattern ‘%%’ results in returning all the record from the table. Hence, the pattern should be written as:

'%#_%' ESCAPE '#'

To search the string “25%”, the following predicate may be used:

LIKE '25|%' ESCAPE '|'

The truth value of LIKE predicate is assigned in accordance with the following rules:

  • If either value to test or the pattern, or control symbol is NULL, the truth value is UNKNOWN;
  • Otherwise, if the value to test and the pattern have zero-length, the truth value is TRUE;
  • Otherwise, if the value to test matches the pattern, LIKE predicate evaluates to TRUE;
  • If none of the above conditions is met, LIKE predicate evaluates to FALSE.

Suggested exercises: 44, 45, 74

LIKE predicate and regular expressions

LIKE predicate in its standard edition does not support regular expressions, though a number of implementations (in particular, Oracle) supposes their usage, expanding the standard.

In SQL Server 2005/2008, the use of regular expressions is possible through CLR, i.e. by means of Visual Studio languages which can be used for writing stored procedures and UDFs.

However, Transact-SQL, besides standard wildcard characters ("%" and “_”), introduces pair of additional symbols which make LIKE predicate more flexible as a tool. These symbols are:

  • - a single symbol from a character set (for example, [zxy]) or a range ([a-z]) specified in square brackets. Thus, it is possible to list multiple ranges at once (for example, [0-9a-z]);

  • ^ - which in combination with square brackets excludes from the search sample the characters listed in a set or a range.

Let’s explain use of these symbols by examples.

SELECT * FROM   
(  
SELECT '5%' name    UNION ALL   
SELECT '55'              UNION ALL  
SELECT '5%%'          UNION ALL   
SELECT '3%%'          UNION ALL   
SELECT 'a5%%'        UNION ALL   
SELECT 'abc'             UNION ALL   
SELECT 'abc 5% cde' UNION ALL   
SELECT '5c2e'           UNION ALL   
SELECT 'C2H5OH'      UNION ALL   
SELECT 'C25OH'        UNION ALL   
SELECT 'C54OH'   
) x  
  
 /* 1 */  
 --WHERE name LIKE'5%' -- begins with 5  
 /* 2 */  
 --WHERE name LIKE '5[%]' -- 5%  
 /* 3 */  
 --WHERE name LIKE '5|%' ESCAPE '|'-- 5%  
 /* 4 */  
 --WHERE name LIKE '%5|%%' ESCAPE '|' -- 5% in any place of a string  
 /* 5 */  
 --WHERE name LIKE '[0-9][a-zA-Z]%' -- the first is a digit, the second is a letter  
 /* 6 */    
 --WHERE name LIKE '[a-z][0-9]%' -- the first is a letter, the second is a digit  
 /* 7 */   
 --WHERE name LIKE '[^0-9]%' -- begins with not a digit  
 /* 8 */  
 --WHERE name LIKE '%[02468]%' -- contains an even digit  
 /* 9 */  
 --WHERE name LIKE '%[02468][13579]%' -- a combination even-odd
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

In the given query some data are generated, for search over which the LIKE predicate is used. Nine examples is accordingly 9 commented (–) WHERE clauses. For check of results of executing of above query on the site, erase preliminary the comment for one of the lines beginning with WHERE. For those who cannot use a site, I shall present the results of executing these examples below.

1. All the strings, which begin with 5:

name
5%
55
5%%
5c2e

2. Searching of the string ‘5%’. The character in brackets is perceived as a usual single symbol:

name
5%

3.Other solution which is similar to the second one, but it is using the ESCAPE clause, specifying that it is necessary to perceive “%” as an usual symbol.

4. Searching of the substring ‘5%’ being at any place in the string:

name
5%
5%%
a5%%
abc 5% cde

5. Searching of a string in which the first symbol is a digit, but the second one is a letter:

name
5c2e

6. Searching of a string in which the first symbol is a letter, and the second one is a digit. A variant for case-independent comparisons:

name
a5%%
C2H5OH
C25OH
C54OH

7. Searching of a string which begins not with digit:

name
a5%%
abc
abc 5% cde
C2H5OH
C25OH
C54OH

8. Searching of a string which contains even digit:

name
5c2e
C2H5OH
C25OH
C54OH

9. Searching of a string which contains successively going even and odd digits:

name
C25OH

Suggested exercises:35