DB2 - SQL Like Operator

Wildcards are special characters used to match parts of a value in the where clause.

To use wildcards in search clauses, the LIKE operator must be used.

Wildcard searching can be used only with text fields (strings).

There are two wildcards often used in conjunction with the LIKE operator:

1. % - Means match any number of occurrences of any character.

2. _ - The underscore represents a single character

Syntax:

SELECT column1, column2, ...
  FROM table_name
  WHERE columnN LIKE pattern;

You can also combine any number of conditions using AND or OR operators.

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

DB2 Database:

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

Example 1:

The following SQL statement selects all Product with a ProductName starting with "H":

SELECT * FROM Product
    WHERE ProductName LIKE 'H%';

Result:

Example 2:

The following SQL statement selects all Product with a ProductName ending with "e":

SELECT * FROM Product
    WHERE ProductName LIKE '%e';

Result:

Example 3:

The following SQL statement selects all Product with a ProductName that have "dd" in any position:

SELECT * FROM Product
    WHERE ProductName LIKE '%dd%';

Result:

Example 4:

The following SQL statement selects all Product with a ProductName that have "o" in the second position:

SELECT * FROM Product
    WHERE ProductName LIKE '_o%';

Result:

Example 5:

The following SQL statement selects all Product with a ProductName that starts with "H" and ends with "e":

SELECT * FROM Product
    WHERE ProductName LIKE 'H%e';;

Result:

Example 6:

The following SQL statement selects all Product with a ProductName that does NOT start with "H":

SELECT * FROM Product
    WHERE ProductName NOT LIKE 'H%';

Result:

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