LIKE predicate

The LIKE predicate searches for strings that have a certain pattern. The pattern is specified by a string in which the underscore and percent sign have special meanings. Trailing blanks in a pattern are a part of the pattern.


>>-match-expression--+-----+--LIKE--pattern-expression--+---------------------------+-><
                     '-NOT-'                            '-ESCAPE--escape-expression-'

If the value of any of the arguments is null, the result of the LIKE predicate is unknown.

The match-expression, pattern-expression, and escape-expression must identify strings or numbers. A numeric argument is cast to a character string before evaluating the predicate. For more information about converting numeric to a character string, see VARCHAR. The values for match-expression, pattern-expression, and escape-expression must either all be binary strings or none can be binary strings. The three arguments can include a mixture of character strings and graphic strings.

None of the expressions can yield a distinct type. However, it can be a function that casts a distinct type to its source type.

If the operands of the predicate are SBCS data, mixed data, or Unicode data, and if the collating sequence in effect at the time the statement is executed is not *HEX, then the comparison of the operands is performed using weighted values for the operands. The weighted values are based on the collating sequence. An ICU collating sequence is not allowed with a LIKE predicate.

With character strings, the terms character, percent sign, and underscore in the following discussion refer to single-byte characters. With graphic strings, the terms refer to double-byte or Unicode characters. With binary strings, the terms refer to the code points of those single-byte characters.

match-expression

An expression that specifies the string that is to be examined to see if it conforms to a certain pattern of characters.

LIKE pattern-expression

An expression that specifies the string that is to be matched.

Simple description: A simple description of the LIKE pattern is as follows:

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

If the pattern-expression needs to 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.

Rigorous description: Let x denote a value of match-expression and y denote the value of pattern-expression.

The string y is interpreted as a sequence of the minimum number of substring specifiers so each character of y is part of exactly one substring specifier. A substring specifier is an underscore, a percent sign, or any nonempty sequence of characters other than an underscore or a percent sign.

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

A substring of x is a sequence of zero or more contiguous characters and each character of x is part of exactly one substring.
If the nth substring specifier is an underscore, the nth substring of x is any single character.
If the nth substring specifier is a percent sign, the nth substring of x 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 x is equal to that substring specifier and has the same length as that substring specifier.
The number of substrings of x is the same as the number of substring specifiers.

It follows that if y is an empty string and x is not an empty string, the result is false. Similarly, it follows that if Start of change x End of change is an empty string and y is not an empty string consisting of other than percent signs, the result is false.

The predicate xNOT LIKEy is equivalent to the search condition NOT(x LIKE y).

If necessary, the CCSID of the match-expression, pattern-expression, and escape-expression are converted to the compatible CCSID between the match-expression and pattern-expression.

Mixed data: If the column is mixed data, the pattern can include both SBCS and DBCS characters. The special characters in the pattern are interpreted as follows:

An SBCS underscore refers to one SBCS character.
A DBCS underscore refers to one DBCS character.
A percent sign (either SBCS or DBCS) refers to any number of characters of any type, either SBCS or DBCS.
Redundant shifts in match-expression and pattern-expression are ignored.¹

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

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

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 or UCS-2
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 EBCDIC data, a full-width _ character matches one DBCS character.)

Binary data: If the column is 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 one 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 a correct length is not specified, the select will 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%     '

This pattern requests the database manager to search for all values that start with WYSE and end with five blank spaces. If you intended to search for only the values that start with 'WYSE' you should assign the value 'WYSE%%%%%%' to the variable.

ESCAPE escape-expression

An expression that specifies a character to be used to modify the special meaning of the underscore (_) and percent (%) characters in the pattern-expression. This allows the LIKE predicate to be used to match values that contain the actual percent and underscore characters. The following rules apply the use of the ESCAPE clause and the escape-expression:

The escape-expression must be a string of length 1.²
The pattern-expression must not contain the escape character except when followed by the escape character, percent, or underscore.
For example, if '+' is the escape character, any occurrences of '+' other than '++', '+_', or '+%' in the pattern-expression is an error.
The escape-expression can be a parameter marker.

The following example 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

Search for the string ‘SYSTEMS' appearing anywhere within the PROJNAME column in the PROJECT table.

   SELECT PROJNAME
     FROM PROJECT
     WHERE PROJECT.PROJNAME LIKE '%SYSTEMS%'

Example 2

Search for a string with a first character of ‘J' that is exactly two characters long in the FIRSTNME column of the EMPLOYEE table.

SELECT FIRSTNME
     FROM EMPLOYEE
     WHERE EMPLOYEE.FIRSTNME LIKE 'J_'

Example 3

In this example:

SELECT *
     FROM TABLEY
     WHERE C1 LIKE 'AAAA+%BBB%' ESCAPE '+'

Start of change '+' is the escape character and indicates that the search is for a string that starts with 'AAAA%BBB'. The '+%' is interpreted as a single occurrence of '%' in the pattern. End of change

Example 4

In the following table of EBCDIC examples, assume COL1 is mixed data. The table shows the results when the predicates in the first column are evaluated using the COL1 values from the second column:

Results when the predicates in the first column are evaluated using the COL1 values from the second column.

Example 5

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 6

The RESUME column in sample table EMP_RESUME is defined as a CLOB. If the variable LASTNAME has a value of 'JONES', the following statement selects the RESUME column when the string JONES appears anywhere in the column.

   SELECT RESUME
     FROM EMP_RESUME
     WHERE RESUME LIKE '%'||LASTNAME||'%'

¹ Redundant shifts are normally ignored. To guarantee that they are ignored, however, specify the IGNORE_LIKE_REDUNDANT_SHIFTS query attribute. See Database Performance and Query Optimization for information about setting query attributes.

² If it is NUL-terminated, a C character string variable of length 2 can be specified.