Similarities of character data

You can use the LIKE predicate to specify a character string that is similar to the column value of rows that you want to select.

A LIKE pattern must match the character string in its entirety.

  • Use a percent sign (%) to indicate any string of zero or more characters.
  • Use an underscore (_) to indicate any single character.

You can also use NOT LIKE to specify a character string that is not similar to the column value of rows that you want to select.

Examples: How to select values similar to a string of unknown characters

The percent sign (%) means any string or no string.

Begin general-use programming interface information.
Example
The following query selects data from each row for employees with the initials D B: 
SELECT FIRSTNME, LASTNAME, DEPT
  FROM EMP
  WHERE FIRSTNME LIKE 'D%' AND LASTNAME LIKE 'B
Example
The following query selects data from each row of the department table, where the department name contains CENTER anywhere in its name: 
SELECT DEPTNO, DEPTNAME
  FROM DEPT
  WHERE DEPTNAME LIKE '
Example
Assume that the DEPTNO column is a three-character column of fixed length. You can use the following search condition to return rows with department numbers that begin with E and end with 1:
…WHERE DEPTNO LIKE 'E%1';

In this example, if E1 is a department number, its third character is a blank and does not match the search condition. If you define the DEPTNO column as a three-character column of varying length instead of fixed length, department E1 would match the search condition. Varying-length columns can have any number of characters, up to and including the maximum number that was specified when the column was created.

Example
The following query selects data from each row of the department table, where the department number starts with an E and contains a 1:
SELECT DEPTNO, DEPTNAME
  FROM DEPT
  WHERE DEPTNO LIKE 'E
End general-use programming interface information.

Example: How to select a value similar to a single unknown character

The underscore (_) means any single character.

Begin general-use programming interface information.

Consider the following query:

SELECT DEPTNO, DEPTNAME
   FROM DEPT
   WHERE DEPTNO LIKE 'E_1';

In this example, 'E_1' means E, followed by any character, followed by 1. (Be careful: '_' is an underscore character, not a hyphen.) 'E_1' selects only three-character department numbers that begin with E and end with 1; it does not select the department number 'E1'.

End general-use programming interface information.