Expansion of Like-predicate

The like-predicate tests whether a character value matches the pattern of another character value.
idmscu19
The like-predicate tests whether a character value matches the pattern of another character value.
This article describes the following information:
2
2
Syntax
Expansion of like-predicate
►►─── 
value-expression
 ───────────────────────────────────────────────────────►  ►─┬───────┬─ LIKE ─┬─ 
'pattern'
 ────────────────────────────────┬────────────►    └─ NOT ─┘        ├─ G 
'graphics pattern'
 ─────────────────────┤                     ├─ 
host-variable
 ────────────────────────────┤                     ├─ 
special-register
 ─────────────────────────┤                     ├─ 
dynamic-parameter-marker
 ─────────────────┤                     ├─ 
routine-parameter
 ────────────────────────┤                     └─ 
local-variable
 ───────────────────────────┘  ►─┬──────────────────────────────────────────────┬───────────────────────────►◄    └─ ESCAPE ─┬─ '
escape-character
' ────────────┬─┘               ├─ 
host-variable
 ─────────────────┤               ├─ 
dynamic-parameter-marker
 ──────┤               ├─ 
routine-parameter
 ─────────────┤               └─ 
local-variable
 ────────────────┘
Parameters
  • value-expression
    Specifies a value to be tested against a pattern or other value.
    Value-expression
    must have a data type of CHARACTER, VARCHAR, BINARY, GRAPHIC, or VARGRAPHIC.
  • NOT
    Reverses the test. NOT directs CA IDMS to test whether a specified value does not match the specified pattern.
  • LIKE
    Identifies the pattern to which the value being tested is compared.
  • '
    pattern
    '
    Specifies a character string literal to be used as the test pattern.
    A test pattern can include wildcard characters:
Character
Meaning
_ (underscore)
Represents any single character
% (percent sign)
Represents any string of zero or more characters
Wildcard characters can be used in any combination and any number of times in a test pattern.
  • G'
    graphics-pattern
    '
    Specifies a double-byte character literal to be used as a test pattern.
    Wildcard characters can be used as described for
    pattern
    except that the wildcard characters are the double-byte equivalents of the characters shown in the table above, and they are used to represent double-byte characters.
  • host-variable
    Identifies a host variable that contains the character value to be used as the test pattern. The host variable must have been declared in an SQL declaration section and must be an elementary item instead of a group field.
  • special-register
    Identifies a special register that contains the value to which the value being tested is compared.
    CURRENT TIMEZONE may not be specified for
    special-register
    . For expanded
    special-register
    syntax, see Expansion of Special-register.
  • dynamic-parameter-marker
    Indicates that a dynamic parameter is used to contain the character value for the test pattern.
  • host-variable
    Identifies a local variable that contains the character value to be used as the test pattern. The local variable must have been declared in an SQL declaration statement.
  • routine-parameter
    Identifies a routine parameter that contains the character value to be used as the test pattern. The routine parameter must have been defined in the parameter-definition of the SQL routine.
  • local-variable
    Specifies a local variable to be used in the value-expression.
  • ESCAPE
    The ESCAPE option allows the designation of an escape character for the pattern. The option must specify a one byte character value. If it appears in the pattern string, the escape character must be immediately followed by either a wildcard character or by another instance of the escape character. When this happens, the leading escape character is dropped from the match and the following character (wildcard or escape) is treated at face value instead of as a special character. For example, LIKE 'A_%' matches all values beginning with A, while LIKE 'AZ_%' ESCAPE 'Z' matches all values beginning with A_.
    Escape characters are not supported in installations with active DBCS support.
  • '
    escape character
    '
    Specifies the character to be used as the escape character.
    Escape-character
    must be a one-byte character value.
  • host-variable
    Specifies the character to be used as the escape character.
    Escape-character
    must be a one-byte character field.
  • dynamic-parameter-marker
    Specifies that the one-byte escape character is supplied through a dynamic parameter.
  • routine-parameter
    Specifies the character to be used as the escape character.
    Escape-character
    must be a one-byte character field.
  • local-variable
    Specifies the character to be used as the escape character.
    Escape-character
    must be a one-byte character field.
Usage
Truth Value of a LIKE Predicate without NOT
The result of a LIKE predicate that does
not
include NOT is:
  • True when the value being tested matches the test pattern
  • False when the value being tested does not match the test pattern
  • Unknown when either the value being tested or the test pattern is null
Truth Value of a LIKE Predicate with NOT
The result of a LIKE predicate that includes NOT is:
  • True when the value being tested does not match the test pattern
  • False when the value being tested matches the test pattern
  • Unknown when either the value being tested or the test pattern is null
Equivalence
If '
pattern
' contains no wildcard character, the LIKE predicate is the equivalent of a comparison predicate using an equal sign, with the restriction that the lengths of the two values being compared must be identical. This restriction distinguishes a test for a match from a test for equality.
Evaluation of Trailing Blanks
If
value-expression
containing a character string with trailing blanks is compared to the same character string without trailing blanks in a LIKE predicate, the result is false. For example, 'ABC ' is not like 'ABC'. Similarly, 'ABC' is not like 'ABC '.
Graphics and Character Values
If
value-expression
is a character value, then the search pattern must also be a character value. If
value-expression
is a graphics value, then the search pattern must also be a graphics value.
Using Host Variables and Dynamic Parameters As Test Patterns
The value of the test pattern can be supplied through a host variable or a dynamic parameter. The value of the variable or parameter can include wildcard characters as described above. For example, assume you code the following:
02  PATTERN   PIC X(10).  .... MOVE '%ABC%' TO PATTERN.  .... SELECT .... WHERE .... LIKE :PATTERN;
The pattern being used is '%ABC% ', which means 0 to n of anything followed by ABC, followed by 0 to n of anything, followed by 5 spaces. This doesn't yield the same result as:
SELECT .... WHERE .... LIKE '%ABC%';
which means 0 to n of anything, followed by ABC, followed by 0 to n of anything.
Examples
Using the Underscore in a Pattern
The following SELECT statement identifies the consultants working on projects with four-character identifiers where the middle two characters are 2 and 0:
select con_id, con_lname    from consultant    where proj_id like '_20_';
Using the Percent Sign in a Pattern
The following SELECT statement identifies all employees whose last names begin with A or B:
select emp_fname, emp_lname, dept_id    from employee    where emp_lname like 'A%'       or emp_lname like 'B%';
More Information
  • For more information about character string literals, see Literals.
  • For more information about host variables, see Host Variables.
  • For more information about declaring local variables, see Local Variables.
  • For more information about declaring routine-parameters, see Routine Parameters.