Predicates › LIKE Predicate

LIKE Predicate

Use the LIKE predicate to search for strings that have a certain pattern. The pattern is specified by a string in which the underscore and percent sign have special meaning. Following is the syntax diagram for the LIKE predicate:.

►►─ column-name ─┬───────┬─ LIKE ─┬─ host-variable ─┬─────────────────────────►
                 └─ NOT ─┘        └─ literal ───────┘

 ►─┬──────────────────────────────┬───────────────────────────────────────────►◄
   └─ ESCAPE ─┬─ host-variable ─┬─┘
              └─ literal ───────┘

Description

column-name

Specify the name of a column in a table or view. The column-name can be any expression that has a character string data type.

NOT

A keyword. Use NOT to specify values that do not match the comparison.

LIKE host-variable

Specify a host-variable. The host-variable must identify a variable that is described in the program under the rules for declaring string host-variables. (See Host Variables for more information.) The host-variable must contain data of the same type as the specified column.

Specify a pattern for the comparison as part of the host-variable. The pattern must contain character data. Use the percent sign (%) in your pattern to indicate a substring of zero or more characters. Use the underscore (_) in your pattern to indicate a single character.

See the examples at the end of this section for use of the percent sign and underscore as substring specifiers.

Note: When you specify a pattern (such as xxx%) that provides the prefix of the desired value, the Compound Boolean Selection Facility performs an index scan, which gives a performance gain. Any other use of a pattern or the use of a specific value causes a data scan, which is slower than an index scan.

LIKE literal

Specify a literal, and specify a pattern for the comparison as part of the literal. The pattern must contain character data. Use the percent sign (%) in your pattern to indicate a substring of zero or more characters. Use the underscore (_) in your pattern to indicate a single character.

See the examples at the end of this section for use of the percent sign and underscore as substring specifiers.

Note: When you specify a pattern (such as xxx%) that provides the prefix of the desired value, the Compound Boolean Selection Facility performs an index scan, which gives a performance gain. Any other use of a pattern or the use of a specific value causes a data scan, which is slower than an index scan.

ESCAPE host-variable

Specify a host-variable. The host-variable must identify a variable that is described in the program under the rules for declaring string host-variables. (See Host Variables for more information.) The host-variable must contain data of the same type as the specified column.

Specify an escape character as part of the host-variable. An escape character is a valid character that is one byte in length. Use the escape character when you want to include the percent sign (%) or the underscore(_) as part of the pattern (as previously discussed in the description of LIKE host-variable).

When an escape character is used in the pattern of the LIKE predicate, it must be followed by the percent character (%), the underscore character (_), or another escape character. The word character in this context refers to either a Single-Byte Character Set (SBCS) or Double-Byte Character Set (DBCS) character. Any other use of the escape character in the pattern of the LIKE predicate is invalid.

See the examples at the end of this section for use of the escape character.

ESCAPE literal

Specify a literal, and specify an escape character as part of the literal. An escape character is a valid character that is one byte in length. Use the escape character when you want to include the percent sign (%) or the underscore(_) as part of the pattern (as previously discussed in the description of LIKE literal).

When an escape character is used in the pattern of the LIKE predicate, it must be followed by the percent character (%), the underscore character (_), or another escape character. The word character in this context refers to either a Single-Byte Character Set (SBCS) or Double-Byte Character Set (DBCS) character. Any other use of the escape character in the pattern of the LIKE predicate is invalid.

See the examples at the end of this section for use of the escape character.

If the column contains character data, the terms "character," "percent sign," and "underscore" in the following discussion refer to EBCDIC (Single-Byte Character Set (SBCS)) characters. If the column contains GRAPHIC data, those terms refer to Double-Byte Character Set (DBCS) characters.

This predicate is best explained by examples. The following description is intended for those who require a rigorous definition.

1. Let x denote a value of the column.
2. Let y denote the string specified by the second operand.
3. The string y is interpreted as a sequence of the minimum string of substring specifiers such that each character of y is part of exactly one substring specifier. A substring specifier is:
   • An underscore (_)
   • A percent sign (%)
   • Any nonempty sequence of characters other than an underscore or a percent sign
4. The result is either true or false.
5. The result is true if x and y are both empty strings or there exists a partitioning of x into substrings such that:
   1. A substring of x is a sequence of zero or more continuous characters and each character of x is part of exactly one substring.
   2. If the nth substring specifier is an underscore, the nth substring of x is any single character.
   3. If the nth substring specifier is a percent sign, the nth substring of x is any sequence of zero or more characters.
   4. If the nth substring specifier is neither an underscore nor a percent sign, the nth substring of x is equal to that substring specifier and has the same length as that substring specifier.
   5. The number of substrings of x is the same as the number of substring specifiers.

Predicate x NOT LIKE y is equivalent to search condition NOT(x LIKE y).

If MIXED data is in effect, the column identified by column-name may contain Double-Byte Character Set (DBCS) characters, as may the host-variable or string literal. In that case, the special characters in y are interpreted as follows:

• An EBCDIC (Single-Byte Character Set (SBCS)) character underscore refers to one EBCDIC character.
• A double-byte underscore refers to one DBCS character.
• A percent sign, either EBCDIC or double-byte, refers to any number of characters of any type, either EBCDIC or double-byte.

Examples

Some examples of the LIKE predicate are:

Example 1: This example is true if JOHN appears anywhere within the value of NAME. The first percent sign indicates any character substring which precedes JOHN while the second percent sign indicates any substring which follows JOHN.

 NAME LIKE '%JOHN%'

Example 2: The following example is true if the value of CODE has a length of three characters and the first character is A and the third character is C. The underscore indicates any single character between the A and the C.

 CODE LIKE 'A_C'

Example 3: The following example is true if one hundred percent (100%) appears at the end of the value of INCREASE. The first percent sign indicates any substring which precedes 100. The pound sign (#) indicates that the following percent sign is to be part of the search pattern. Assignment of the pound sign as the escape character is accomplished by ESCAPE '#'.

 INCREASE LIKE '%100#%' ESCAPE '#'