DB2 - SQL Wildcards

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Use the following wildcard characters to search for and list DB2 objects:

• _ (underscore) matches any single character.

• % (percent sign) or * (asterisk) matches a string of zero or more characters.

Wildcards can also be used in combinations.

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

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:

STUDENT_NAME LIKE '%SMITH%'

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

STUDENT_NAME LIKE 'SP%%%%AN'
STUDENT_NAME LIKE 'SP%AN'

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1: Using the % Wildcard

The following SQL statement selects all Product with a Category starting with "Acc":

SELECT * FROM Product
    WHERE Category LIKE 'Acc%';

Result:

Example 2: Using the % Wildcard

The following SQL statement selects all Product with a Category containing the pattern "or":

SELECT * FROM Product
    WHERE Category LIKE '%or%';

Result:

Example 3: Using the _ Wildcard

The following SQL statement selects all Product with a Category starting with any character, followed by "ikes":

SELECT * FROM Product
    WHERE Category LIKE '_ikes';

Result:

Example 4: Using the _ Wildcard

The following SQL statement selects all Product with a Category starting with "Accessorie" and end with any character.

SELECT * FROM Product
    WHERE Category LIKE 'Accessorie_';

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!