7.19.4 LIKE predicate
The LIKE predicate is used to retrieve data that contains a specific character string.
- Organization of this subsection
(1) Specification format
LIKE-predicate ::= match-value [NOT] LIKE pattern-character-string [ESCAPE escape-character] match-value ::= value-expression pattern-character-string ::= value-expression escape-character ::= value-expression
(2) Explanation of specification format
- match-value:
-
Specifies the data to be retrieved in the form of a value expression. For details about value expressions, see 7.20 Value expression.
You must specify CHAR or VARCHAR type data for match-value.
- NOT:
-
If NOT is specified, values that do not match the specified pattern character string will become the target of retrieval.
- pattern-character-string:
-
Specifies the pattern character string in the form of a value expression. For details about value expressions, see 7.20 Value expression.
You must specify CHAR or VARCHAR type data for pattern-character-string.
The special characters _ (underscore) and % (percent) can be specified in the pattern character string. The special character _ denotes any single character, and % denotes a character string of zero or more characters. These special characters can be used in searches such as the following.
-
Five-character character strings that begin with UN: 'UN_ _ _'
-
Character strings that include OR: '%OR%'
When _ or % appear in a pattern character string, they are considered special characters and are not treated as normal characters. If you want _ or % to be treated as a normal character, you must specify an escape character.
-
- ESCAPE escape-character:
-
Specifies an escape character in the form of a value expression. For details about value expressions, see 7.20 Value expression.
Specify one byte of CHAR or VARCHAR type data for escape-character.
When you specify an escape character, special characters in the pattern character string (specifically, special characters immediately following the escape character) can be treated as normal characters.
Examples specifying the special character %:
LIKE 'ABC@%' ...1 LIKE 'ABC@%' ESCAPE '@' ...2
In 1, because % is treated as a special character, character strings beginning with 'ABC@' will be retrieved. In 2, because % is treated as a normal character, character strings beginning with 'ABC%' will be retrieved.
For details about how pattern character strings are handled when an escape character is specified, see (4) How pattern character strings are handled when an escape character is specified.
(3) Examples of pattern character strings
Typical examples of pattern character strings are given in the following table.
No. |
Item |
Pattern character string |
Meaning |
Pattern character string specification example |
Pattern-matched character strings |
---|---|---|---|---|---|
1 |
Starts-with match |
xxx% |
The leading portion of the character string is xxx. |
'ACT%' |
Character strings beginning with ACT, such as ACT, ACTOR, and ACTION. |
2 |
Ends-with match |
%xxx |
The trailing portion of the character string is xxx. |
'%ING' |
Character strings ending with ING, such as ING, BEING, and HAVING. |
3 |
Contains match |
%xxx% |
The character string contains xxx at some position. |
'%OR%' |
Character strings containing OR, such as OR, MORE, and COLOR. |
4 |
Full match |
xxx |
The character string is equal to xxx. |
'EQUAL' |
EQUAL |
5 |
Partial match |
_..._xxx_..._ |
|
'_I_' |
Three-letter character strings, in which the second character is I, such as BIT, HIT, and KIT. |
6 |
'_ _T_ _ _ _' |
Seven-letter character strings, in which the third character is T, such as HITACHI. |
|||
7 |
Other |
xxx%yyy |
The leading portion of the character string is xxx and the trailing portion is yyy. |
'O%N' |
Character strings that begin with O and end with N such as ON, OWN, and ORIGIN. |
8 |
%xxx%yyy% |
The character string contains xxx at some position and yyy at a subsequent position. |
'%O%N%' |
Character strings that contain O, and have an N in any subsequent position, such as ON, ONE, DOWN, and COUNT. |
|
9 |
xxx_..._yyy% |
|
'CO_ _ECT%' |
Character strings that begin with CO and contain the string ECT in the 5th through 7th character positions, such as CORRECT, CONNECTER, and CONNECTION. |
- Notes:
-
-
xxx and yyy denote any characters strings that do not include the characters % or _.
-
Because a space is regarded as a character for comparison purposes, a comparison with data that has trailing spaces generates a FALSE result.
-
(4) How pattern character strings are handled when an escape character is specified
This subsection describes how pattern character strings are handled when an escape character is specified. In the examples below, the escape character is the at mark (@).
-
When the escape character is immediately followed by a special character, the special character is treated as a normal character.
Example 1:
LIKE 'AB@%C%' ESCAPE '@'
In this case, because the special character after the @ mark is treated as a normal character, character strings starting with AB%C will be retrieved, such as AB%C and AB%CDE.
Example 2:
LIKE 'AB@_C%' ESCAPE '@'
In this case, because the special character after the @ mark is treated as a normal character, character strings starting with AB_C will be retrieved, such as AB_C and AB_CDE.
-
When the escape character is immediately followed by a normal character, the escape character is skipped.
Example:
LIKE 'ABC@D' ESCAPE '@' → equivalent to LIKE 'ABCD'
In this case, the @ mark is skipped.
-
Two consecutive escape characters are treated as a single normal character.
Example 1:
LIKE 'AB@@C' ESCAPE '@' → equivalent to LIKE 'AB@C'
In this case, the two at marks (@@) are treated as the single normal @ mark.
Example 2:
LIKE 'AB@@@C' ESCAPE '@' → equivalent to LIKE 'AB@C'
In this case, the first two at marks (@@) are treated as the single normal @ mark. The third @ mark is skipped because the character after it is a normal character.
Example 3:
LIKE 'AB@@@@C' ESCAPE '@' → equivalent to LIKE 'AB@@C'
In this case, the first two at marks (@@) are treated as the single normal @ mark. The next two at marks (the third and fourth @ marks) are also treated as the single normal @ mark.
Example 4:
LIKE 'AB@@C@%D%' ESCAPE '@'
In this case, character strings starting with AB@C%D will be retrieved, such as AB@C%D and AB@C%DE.
-
When no character follows the escape character, the escape character is skipped.
Example:
LIKE 'ABC@' ESCAPE '@' → equivalent to LIKE 'ABC'
(5) Evaluation of the predicate
If match-value matches the pattern in pattern-character-string the result is TRUE; otherwise, it is FALSE.
If NOT is specified, and match-value does not match the pattern in pattern-character-string, the result is TRUE; otherwise, it is FALSE.
If the result of match-value or pattern-character-string has a null value, the result of the predicate is unknown.
If the length of match-value is 0 bytes or 0 characters, the result of the LIKE predicate will be TRUE only in the following cases:
-
If the pattern character string has a length of 0 bytes or 0 characters
-
If the pattern character string is specified as a dynamic parameter, and its input value is '%'
-
If the pattern character string is specified as the literal '%'
In addition, if the length of pattern-character-string is 0 bytes or 0 characters, the result of the LIKE predicate will be TRUE only if the length of match-value is 0 bytes or 0 characters.
(6) Rules
(a) Rules for match-value
-
The special characters underscore (_) and percent sign (%) in match-value must be specified as single-byte (minimum byte) characters.
-
If a dynamic parameter is specified by itself for match-value, the assumed data type of the dynamic parameter will be VARCHAR(32000).
(b) Rules for pattern-character-string
-
The length of the pattern character string includes the special characters _ and %.
-
If a percent sign (%) is not specified in pattern-character-string, and the length of the data in match-value is different from the length of the pattern character string, the predicate is not TRUE.
-
If you specify a dynamic parameter by itself for pattern-character-string, the assumed data type and data length of the dynamic parameter will be as shown in the following table.
Table 7‒5: Assumed data type and data length of the dynamic parameter (when you specify a dynamic parameter by itself for pattern-character-string) Condition
Assumed data type of dynamic parameter
Assumed data length of dynamic parameter
Escape character not specified
VARCHAR type
Data length of the result of match-value
Escape character specified
VARCHAR type
-
If the data length of the result of match value is no more than 32,000 bytes
MIN(data-length-of-the-result-of-match-value × 2, 32,000)
-
If the data length of the result of match value is 32,001 bytes or more
MIN(data-length-of-the-result-of-match-value × 2, 64,000)
-
(c) Rules for escape characters
-
If escape-character has a length of 0 bytes or 0 characters, it is treated as if no escape character was specified.
Examples:
LIKE 'ABC' ESCAPE '' → equivalent to LIKE 'ABC'
LIKE 'ABC' ESCAPE ? → equivalent to LIKE 'ABC' when NULL is specified for the dynamic parameter
-
If a dynamic parameter is specified by itself for escape-character, the assumed data type of the dynamic parameter is VARCHAR(1). In this case the actual length of the data must be 1 byte.
-
Identification of escape characters in the pattern character string is performed character-by-character rather than byte-by-byte.
-
The following table shows the range of character code points that can be specified for the escape character.
Table 7‒6: Range of character code points that can be specified for the escape character Value specified in the environment variable ADBLANG
Range of character code points that can be specified for the escape character
UTF8 (Unicode (UTF-8))
0x00 to 0x7F#
SJIS (Shift-JIS)
0x00 to 0xFF
- #
-
Does not include the Shift-JIS backslash (\: 0x5C) and swung dash (~: 0x7E) characters when they are represented as multi-byte characters in UTF-8.
(7) Examples
- Example 1
-
This example retrieves the customer ID (USERID) and name (NAME) of customers whose name begins with M.
SELECT "USERID","NAME" FROM "USERSLIST" WHERE "NAME" LIKE 'M%'
The underlined portion indicates the LIKE predicate.
- Example 2
-
This example retrieves the customer ID (USERID) and name (NAME) of female customers whose name does not begin with M.
SELECT "USERID","NAME" FROM "USERSLIST" WHERE "NAME" NOT LIKE 'M%' AND SEX='F'
The underlined portion indicates the LIKE predicate.
- Example 3
-
This example searches the product column (GOODS) in the sales table (T_SALES) for products that begin with the character strings in the pattern column (PATTERN) in the pattern table (T_PATTERN).
SELECT "A"."GOODS" FROM "T_SALES" AS "A","T_PATTERN" AS "B" WHERE "A"."GOODS" LIKE "B"."PATTERN" + '%'
The underlined portion indicates the LIKE predicate.
- Example 4
-
This example searches the sales table (T_SALES) and retrieves the product name (GOODS) and sales amount (SALES) for products meeting the following conditions:
-
The name of the product includes an underscore (_)
-
The product is associated with branch code (BRANCH_CODE) A001
Because underscore (_) is a special character, @ is specified as the escape character so that _ will be treated as a normal character.
SELECT "GOODS","SALES" FROM "T_SALES" WHERE "GOODS" LIKE '%@_%' ESCAPE '@' AND "BRANCH_CODE"='A001'
-