The like-predicate tests whether a character value matches the pattern of another character value.
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 ────────────────┘
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.
Reverses the test. NOT directs CA IDMS to test whether a specified value does not match the specified pattern.
Identifies the pattern to which the value being tested is compared.
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.
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.
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.
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.
Indicates that a dynamic parameter is used to contain the character value for the test pattern.
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.
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.
Specifies a local variable to be used in the value-expression.
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_.
Important! Escape characters are not supported in installations with active DBCS support.
Specifies the character to be used as the escape character. Escape-character must be a one-byte character value.
Specifies the character to be used as the escape character. Escape-character must be a one-byte character field.
Specifies that the one-byte escape character is supplied through a dynamic parameter.
Specifies the character to be used as the escape character. Escape-character must be a one-byte character field.
Specifies the character to be used as the escape character. Escape-character must be a one-byte character field.
Truth Value of a LIKE Predicate without NOT
The result of a LIKE predicate that does not include NOT is:
Truth Value of a LIKE Predicate with NOT
The result of a LIKE predicate that includes NOT is:
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.
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%';
|
Copyright © 2014 CA.
All rights reserved.
|
|