LIKE Predicate (like_predicate)

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

Structure

Syntax Syntax

<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'  !>

End of the code.

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