LIKE predicate

The LIKE predicate searches for strings that have a certain pattern.

The match-expression is the string to be tested for conformity to the pattern specified in pattern-expression. Underscore and percent sign characters in the pattern have a special meaning instead of their literal meanings unless escape-expression is specified. For more information, see the description of pattern-expression.

The following rules summarize how a predicate in the form of m LIKE p is evaluated:

If m or p is null, the result of the predicate is unknown.
If m and p are both empty, the result of the predicate is true.
If m is empty and p is not, the result of the predicate is unknown unless p consists of one or more percent signs.
If m is not empty and p is empty, the result of the predicate is false.
Otherwise, if m matches the pattern in p, the result of the predicate is true. The description of pattern-expression provides a detailed explanation on how the pattern is matched to evaluate the predicate to true or false.
The way the pattern is matched to evaluate the predicate changes when LIKE blank insignificant behavior is enabled. For more information, see LIKE blank insignificant behavior subsystem parameter.

The values for match-expression, pattern-expression, and escape-expression must all be character or graphic strings or a mixture of both or they must all be binary strings (BLOBs). None of the expressions can yield a distinct type; however, an expression can be a function that casts a distinct type to its source type.

There are slight differences in what expressions are supported for each argument. The description of each argument lists the supported expressions.

match-expression

An expression that specifies the string to be tested for conformity to a certain pattern of characters.

LIKE pattern-expression

An expression that specifies the pattern of characters to be matched.

The expression can be specified by any one of the following:

A constant
A special register
A variable
A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
An array element specification
A CAST specification whose arguments are any of the above
An expression that concatenates (using CONCAT or ||) any of the above

The expression must also meet these restrictions:

The maximum length of pattern-expression must not be larger than 4000 bytes.
If a host variable is used in pattern-expression, the host variable must be defined in accordance with the rules for declaring string host variables and must not be a structure. For more information about the use of host variables with specific programming languages, see Host variables.
If escape-expression is specified, pattern-expression must not contain the escape character that is identified by escape-expression, except when immediately followed by the escape character, '%', or '_'. For example, if '+' is the escape character, any occurrences of '+' other than '++', '+_', or '+%' in the pattern is an error.

The pattern is used to specify the conformance criteria for values in the match-expression where:

The underscore character (_) represents any single character.
The percent sign (%) represents a string of zero or more characters.
Any other character represents a single occurrence of itself.

If the pattern-expression must include either the underscore or the percent character, the escape-expression is used to specify a character to precede either the underscore or percent character in the pattern. For character strings, the terms character, percent sign, and underscore refer to SBCS characters. For graphic strings, the terms refer to double-byte or UTF-16 characters.

If the pattern is specified in a fixed-length string variable, any trailing blanks are interpreted as part of the pattern. Therefore, it is better to use a varying-length string variable with an actual length that is the same as the length of the pattern. If the host language does not allow varying-length string variables, place the pattern in a fixed-length string variable whose length is the length of the pattern.

A rigorous description of the pattern: This more rigorous description of the pattern ignores the use of the escape-expression. Let `m` denote the value of match-expression and let `p` denote the value of pattern-expression. The string `p` is interpreted as a sequence of the minimum number of substring specifiers so each character of `p` is part of exactly one substring specifier. A substring specifier is an underscore, a percent sign, or any non-empty sequence of characters other than an underscore or a percent sign. The result of the predicate is unknown if `m` or `p` is the null value. Otherwise, the result is either true or false. The result is true if `m` and `p` are both empty strings or there exists a partitioning of `m` into substrings such that: A substring of `m` is a sequence of zero or more contiguous characters and each character of `m` is part of exactly one substring. If the `n`th substring specifier is an underscore, the `n`th substring of `m` is any single character. If the `n`th substring specifier is a percent sign, the `n`th substring of `m` is any sequence of zero or more characters. If the `n`th substring specifier is neither an underscore nor a percent sign, the `n`th substring of `m` is equal to that substring specifier and has the same length as that substring specifier. The number of substrings of `m` is the same as the number of substring specifiers. It follows that if `p` is an empty string and `m` is not an empty string, the result is false. Similarly, if `m` is an empty string and `p` is not an empty string consisting of a value other than percentage signs, the result is false. The predicate `m NOT LIKE p` is equivalent to the search condition `NOT (m LIKE p)`.

A rigorous description of the pattern: This more rigorous description of the pattern ignores the use of the escape-expression.

Let m denote the value of match-expression and let p denote the value of pattern-expression. The string p is interpreted as a sequence of the minimum number of substring specifiers so each character of p is part of exactly one substring specifier. A substring specifier is an underscore, a percent sign, or any non-empty sequence of characters other than an underscore or a percent sign.

The result of the predicate is unknown if m or p is the null value. Otherwise, the result is either true or false. The result is true if m and p are both empty strings or there exists a partitioning of m into substrings such that:

A substring of m is a sequence of zero or more contiguous characters and each character of m is part of exactly one substring.
If the nth substring specifier is an underscore, the nth substring of m is any single character.
If the nth substring specifier is a percent sign, the nth substring of m is any sequence of zero or more characters.
If the nth substring specifier is neither an underscore nor a percent sign, the nth substring of m is equal to that substring specifier and has the same length as that substring specifier.
The number of substrings of m is the same as the number of substring specifiers.

It follows that if p is an empty string and m is not an empty string, the result is false. Similarly, if m is an empty string and p is not an empty string consisting of a value other than percentage signs, the result is false.

The predicate m NOT LIKE p is equivalent to the search condition NOT (m LIKE p).

Mixed data patterns: If match-expression represents mixed data, the pattern is assumed to be mixed data. For ASCII and EBCDIC, the special characters in the pattern are interpreted as follows:

An SBCS underscore refers to one SBCS character.
A DBCS underscore refers to one MBCS character.
A percent sign (either SBCS or DBCS) refers to a string of zero or more SBCS or MBCS characters.

For EBCDIC, redundant shift bytes in match-expression or pattern-expression are ignored.

For Unicode, the special characters in the pattern are interpreted as follows:

An SBCS or DBCS underscore refers to one character (either SBCS or MBCS).
A percent sign (either SBCS or DBCS) refers to a string of zero or more SBCS or MBCS characters.

When the LIKE predicate is used with Unicode data, the Unicode percent sign and underscore use the code points indicated in the following table:

Character	UTF-8	UTF-16
Half-width %	X'25'	X'0025'
Full-width %	X'EFBC85'	X'FF05'
Half-width_	X'5F'	X'005F'
Full-width_	X'EFBCBF'	X'FF3F'

The full-width or half-width % matches zero or more characters. The full-width or half-width_ character matches exactly one character. (For ASCII or EBCDIC data, a full-width _ character matches one DBCS character.)

Binary data patterns: For columns with binary data, the pattern contains bytes. The special bytes in the pattern are interpreted as follows:

The code point for an SBCS underscore (X'6D') refers to 1 byte.
The code point for an SBCS percent (X'6C') refers to any number of bytes.

Parameter marker:

When the pattern specified in a LIKE predicate is a parameter marker and a fixed-length character variable is used to replace the parameter marker, specify a value for the variable that is the correct length. If you do not specify the correct length, the select does not return the intended results.

For example, if the variable is defined as CHAR(10) and the value 'WYSE%' is assigned to that variable, the variable is padded with blanks on assignment. The pattern used is 'WYSE% ', which requests Db2 to search for all values that start with 'WYSE' and end with five blank spaces unless LIKE blank insignificant behavior is in effect. If the search was intended to search only for the values that start with 'WYSE', then assign the value 'WYSE%%%%%%' to the variable.

ESCAPE escape-expression

An expression that specifies the escape character to be used to modify the special meaning of the underscore (_) and percent (%) characters in pattern-expression. Specifying an expression, which is optional, allows the LIKE predicate to explicitly test that the value contains a '%' or '_' in the character positions that you want. The escape character consists of a single SBCS (1 byte) or DBCS (2 bytes) character. An escape clause is allowed for Unicode mixed (UTF-8) data, but is restricted for ASCII and EBCDIC mixed data.

The expression can be specified by:

A constant
A variable
A scalar function whose arguments are any of the above (though nested function invocations cannot be used)
A CAST specification whose arguments are any of the above

The following rules also apply to the use of the ESCAPE clause and escape-expression:

The result of escape-expression must be one SBCS or DBCS character or a binary string that contains exactly 1 byte.
The ESCAPE clause cannot be used if match-expression is mixed data.
If escape-expression is specified by a host variable, the host variable must be defined in accordance with the rules for declaring fixed-length string host variables.
Note: If it is NUL-terminated, a C character string variable of length 2 can be specified.
If the host variable has a negative indicator variable, the result of the predicate is unknown.
The pattern must not contain the escape character except when followed by the escape character, '%' or '_'. For example, if '+' is the escape character, any occurrences of '+' other than '++', '+_', or '+%' in the pattern is an error.

The following table shows the effect of successive occurrences of the escape character, which in this case is the plus sign (+).

Table 1. Effect of successive occurrences of the escape character
When the pattern string is...	The actual pattern is...
`+%`	A percent sign
`++%`	A plus sign followed by zero or more arbitrary characters
`+++%`	A plus sign followed by a percent sign

Examples

Example 1

The following predicate is true when the string to be tested in NAME has the value SMITH, NESMITH, SMITHSON, or NESMITHY. It is not true when the string has the value SMYTHE:

  NAME LIKE '%SMITH%'

Example 2

In the predicate below, a host variable named PATTERN holds the string for the pattern:

  NAME LIKE :PATTERN ESCAPE '+'

Assume that the string in PATTERN has the following value:

  AB+_C_%

Observe that in this string, the plus sign preceding the first underscore is an escape character. The predicate is true when the string being tested in NAME has the value AB_CD or AB_CDE. It is false when this string has the value AB, AB_, or AB_C.

Example 3

The following two predicates are equivalent; three of the four percent signs in the first predicate are redundant.

  NAME LIKE 'AB%%%%CD'
  NAME LIKE 'AB%CD'

Example 4

Assume that a distinct type named ZIP_TYPE with a source data type of CHAR(5) exists and an ADDRZIP column with data type ZIP_TYPE exists in some table TABLEY. The following statement selects the row if the zip code (ADDRZIP) begins with '9555'.

  SELECT * FROM TABLEY
    WHERE CHAR(ADDRZIP) LIKE '9555%'

Example 5

The RESUME column in sample table DSN8D10.EMP_PHOTO_RESUME is defined as a CLOB. The following statement selects the RESUME column when the string JONES appears anywhere in the column.

  SELECT RESUME FROM DSN8D10.EMP_PHOTO_RESUME
    WHERE RESUME LIKE '%JONES%'

Example 6

In the following table, assume COL1 is a column that contains mixed EBCDIC data. The table shows the results when the predicate in the first column is evaluated using the COL1 value in the second column:

Begin figure description. A table shows the Results when the predicates are evaluated with the COL1 values (EBCDIC). The additional description contains a literal representation of the table.

Example 7

In the following table, assume COL1 is a column that contains mixed ASCII data. The table shows the results when the predicate in the first column is evaluated using the COL1 value in the second column:

Begin figure description. A table shows the Results when the predicates are evaluated with the COL1 values (ASCII). The additional description contains a literal representation of the table.

Example 8

In the following table, assume COL1 is a column that contains Unicode data. The table shows the results when the predicate in the first column is evaluated using the COL1 value in the second column:

Table 2. COL1 contain Unicode data
Predicates	COL1 values	Result
WHERE COL1 LIKE 'aaaAB%C	'aaaABDZC'	True
	'aaaABdzxC'	True
	empty string	False
WHERE COL1 LIKE 'aaaAB %C'	'aaaABDZC'	True
	'aaaABdzxC'	True
	empty string	False
WHERE COL1 LIKE ''	'aaaABDZC'	False
	'aaaABdzxC'	False
	empty string	True
WHERE COL1 LIKE '%'	'aaaABDZC'	True
	'aaaABdzxC'	True
	empty string	True
WHERE COL1 LIKE ' %'	'aaaABDZC'	True
	'aaaABdzxC'	True
	empty string	False
WHERE COL1 LIKE ' '	'aaaABDZC'	False
	'aaaABdzxC'	False
	empty string	False

LIKE blank insignificant behavior subsystem parameter

When the LIKE_BLANK_INSIGNIFICANT subsystem parameter is enabled, all of the blanks at the end of a fixed-length string are ignored. This behavior is called LIKE blank insignificant behavior. LIKE blank significant behavior, in which the blanks at the end of fixed-length strings are significant (not ignored), is the default behavior during installation or migration. For variable length strings, blanks are significant.

When you set the LIKE_BLANK_INSIGNIFICANT subsystem parameter, LIKE blank insignificant behavior takes effect the next time an SQL query statement with the LIKE predicate is executed after the statement is bound or prepared. If the statement is not prepared or bound, the LIKE behavior exhibits LIKE blank significant behavior regardless of the subsystem parameter setting.

For the following interfaces, enabling or disabling LIKE blank insignificant behavior takes effect immediately. This applies to both an explicit LIKE predicate (for example, UNLOAD) and an implicit LIKE predicate (for example, table check constraint).

INSERT
UPDATE
MERGE
UNLOAD
REORG
LOAD
CHECK DATA

Before the LIKE predicate is applied, any trailing blanks in a CHARACTER or GRAPHIC column are stripped to the last non-blank character. If the column contains all blanks, the blank in character position 1 is not stripped. After stripping occurs, the LIKE predicate is applied against the stripped column data.

Tip: After you enable the LIKE_BLANK_INSIGNIFICANT subsystem parameter, existing rows might not conform to table check constraints that contain a LIKE predicate. Consider running the CHECK DATA utility on all affected tables to find the records that do not conform to the table check constraint.

The following examples, in which b represents a blank character, demonstrate how the LIKE predicate is evaluated when LIKE blank insignificant behavior is enabled.

SELECT C1
FROM T1
WHERE C1 LIKE '%xyz';

This LIKE predicate will match the following fixed-length strings:

abcxyz
abcxyzb
abcxyzbb
abcxyzbb..b'

While trailing blanks in the column data are insignificant, trailing blanks in the LIKE predicate are significant. The following example, in whichb represents a blank character, applies to when the LIKE predicate contains one or more trailing blanks.

SELECT C1
FROM T1
WHERE C1 LIKE '%xyzbb';

This LIKE predicate will not match the following fixed-length strings:

abcxyz
abcxyzb
abcxyzbb
abcxyzbbb

The following example applies to when the LIKE predicate contains one or more single characters (_) in the last position.

SELECT C1
FROM T1
WHERE C1 LIKE '%xyz_';

This LIKE predicate will not match the following fixed-length strings, because they are all stripped to the 'abcxyz' string:

abcxyz
abcxyzb
abcxyzbb
abcxyzbb..b

The following example applies to when the LIKE predicate contains more than one single character (_) in the last position.

SELECT C1
FROM T1
WHERE C1 LIKE '%xyz__';

This LIKE predicate will not match the following fixed-length strings:

abcxyz
abcxyzb
abcxyzbb
abcxyzbb..b

The following example applies to when the LIKE predicate contains more than one single character (_) and a string of zero or more characters (%) are in the last positions.

SELECT C1
FROM T1
WHERE C1 LIKE '%xyz_%_';
This

This LIKE predicate will not match the following fixed-length strings:

abcxyz
abcxyzb
abcxyzbb
abcxyzbb..b

If the column data contains all blanks, every blank, except the blank in character position one, is stripped before the LIKE predicate is applied. For example, a CHAR(6) column contains the following values:

bbbbbb

The following LIKE predicates will match:

LIKE 'b'
LIKE '_'
LIKE '%'

The following LIKE predicates will not match:

LIKE 'bbbbbbb'
LIKE 'bbbb___'
LIKE '_______'