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>::= _ <match_char>::= <! every character other than % | X'1F' | <underscore> | X'1E' !>
Examples
SQL Tutorial, Searching for Character Strings: LIKE, Negative Conditions: NOT
Explanation
x NOT LIKE y has the same result as NOT(x LIKE y).
x, y |
Result of x LIKE y |
---|---|
x or y is a NULL value |
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 a sequence of several contiguous characters, and each character of x belongs to exactly one substring. The number of substrings of x and y is identical. If the n th pattern element ( pattern_element) of y is a sequence of characters and the n th substring of x is a sequence of 0 or more characters. |
x LIKE y is true |
<expression>
The expression expression like_expression must supply an alphanumeric value or a date or time value.
<pattern_element>
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 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.
ESCAPE
An escape character ESCAPE <expression> must be used if you want to search for an underscore, % oder the hexadecimal value X'1E' or X'1F' in the LIKE predicate.
If ESCAPE <expression> is specified, the corresponding expression must return an alphanumeric value that consists of exactly 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 seen as an independent character.