Background documentationLIKE Predicate (like_predicate) Locate this document in the navigation structure

 

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

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

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