loading..
Русский    English
22:55

LIKE predicate page 1

Syntax:

  1. LIKE::=
  2. < expression FOR calculating the string value>
  3. [NOT] LIKE < expression FOR calculating the string value>
  4. [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.

Pattern Description
'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 'о':

Console
Execute
  1. SELECT *
  2. FROM Ships
  3. WHERE class LIKE '%o';

The result of that query is the following table:

name class launched
Haruna Kongo 1916
Hiei Kongo 1914
Kirishima Kongo 1915
Kongo Kongo 1913
Musashi Yamato 1942
Yamato Yamato 1941

Example 5.4.2

Find all the ships the class names ending with the character 'о' but not with the characters 'go':

Console
Execute
  1. SELECT *
  2. FROM Ships
  3. WHERE class NOT LIKE '%go' AND
  4. class LIKE '%o';

name class launched
Musashi Yamato 1942
Yamato Yamato 1941

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:

  1. '%#_%' ESCAPE '#'

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

  1. 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

Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
today
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.