A LIKE predicate (like_predicate) is a predicate that is used to search for character strings that have a certain pattern. This pattern can be a certain character string or any sequence of characters (whose length may or may not be known).
<like_predicate> ::= <expression> [NOT] LIKE <like_expression> [ESCAPE <expression>]
<like_expression> ::= <expression> |
'<pattern_element>...'
<pattern element> ::= <match string> | <match
set>
<match_string> ::=
% | X'1F'
<match_set> ::= <underscore> | X'1E' |
<match_char>
<underscore> ::= _ (underscore)
<match_char> ::= every character other than %,
X'1F',
_ (underscore), X'1E'
x NOT LIKE y has the same result as NOT(x LIKE y).
|
Result of x LIKE y |
x or y are NULL values |
x LIKE y is undefined |
x and y are non-NULL values |
x LIKE y is either true or false |
x can be split into substrings with the result that: A substring of x is a sequence of 0, 1, or more contiguous characters, and each character of x belongs to exactly one substring. The number of substrings of x and y is identical. If the nth pattern element (pattern_element) of y is a sequence of characters and the nth substring of x is a sequence of 0 or more characters. |
x LIKE y is true |
The expression (expression) in the LIKE expression (like_expression) must supply an alphanumeric value or a date or time value.
If a match_string is specified, this position in the search pattern can be replaced by any number of characters.
If a match_set is specified, this position in the search pattern can be replaced by the exact number of characters specified in the match_set.
If an underscore or X'1E' is used, this position in the pattern can be replaced by any one character.
If match_char is used, this position in the pattern can be replaced by the specified character itself.
An escape character (ESCAPE <expression>) must be used if you want to search for an <underscore>, '%', or the hexadecimal value X'1E' or X'1F' in the LIKE predicate.
If ESCAPE <expression> is specified, the corresponding expression (expression) must supply an alphanumeric value that consists of just one character. If this escape character is contained in the LIKE expression (like_expression), the following character must be one of the special characters <underscore>, %, X’1E’, or X’1F’. This special character is then viewed as standing for itself.
Search for any character string with a minimum
length of 1: LIKE '%_'
Search for a
character string in which a fixed number of characters is known: LIKE'_c_'
Search for a
character string with any number of characters, whereby the character string
must contain an underscore: LIKE
'%:_%'ESCAPE':'
SELECT name, zip FROM customer
WHERE name LIKE '%ft'
Customers whose name ends with 'ft':
NAME |
ZIP |
Datasoft |
90018 |
SELECT name, firstname FROM customer
WHERE name LIKE 'P_____'
Finding all customers whose names consist of six letters and begin with 'P'
NAME |
FIRSTNAME |
Porter |
Jenny |
Peters |
Joseph |
SELECT name, firstname FROM customer
WHERE name LIKE '_%p%'
Customers with a 'p' in their name from the second position
NAME |
FIRSTNAME |
Randolph |
Martin |