7.19.4 LIKE predicate

(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

To Page Top

(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.

To Page Top

(3) Examples of pattern character strings

Typical examples of pattern character strings are given in the following table.

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.

To Page Top

(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 (@).

1. 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.

2. 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.

3. 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.

4. When no character follows the escape character, the escape character is skipped.

   Example:

   LIKE 'ABC@' ESCAPE '@' → equivalent to LIKE 'ABC'

To Page Top

(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.

To Page Top

(6) Rules

To Page Top

(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'

To Page Top