SELECT statement

Predicates II

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.

Example 5.4.1

Find all the ships the class names of those end with the character 'о':

Console

Execute

SELECT *
FROM Ships
WHERE class LIKE '%o';

SELECT * FROM Ships WHERE class LIKE '%o';

    Scheme

The result of that query is the following table:

Example 5.4.2

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

Console

Execute

SELECT *
FROM Ships
WHERE class NOT LIKE '%go' AND 
 class LIKE '%o';

SELECT * FROM Ships WHERE class NOT LIKE '%go' AND class LIKE '%o';

    Scheme

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.