Pattern matching is a versatile way of identifying character data. In SQL, the LIKE keyword is used to search for patterns. Pattern matching employs wildcard characters to match different combinations of characters.

The LIKE keyword indicates that the following character string is a matching pattern. LIKE is used with character data.

The syntax for LIKE is:

expression [ NOT ] LIKE match-expression

The expression to be matched is compared to a match-expression that can include these special symbols:

You can match the column data to constants, variables, or other columns that contain the wildcard characters displayed in the table. When using constants, you should enclose the match strings and character strings in single quotes.

Examples

All the following examples use LIKE with the Surname column in the Contacts table. Queries are of the form:

SELECT Surname
   FROM Contacts
   WHERE Surname LIKE match-expression;

The first example would be entered as

SELECT Surname
   FROM Contacts
   WHERE Surname LIKE 'Mc%';

Wildcards require LIKE

Wildcard characters used without LIKE are interpreted as string literals rather than as a pattern: they represent exactly their own values. The following query attempts to find any phone numbers that consist of the four characters 415% only. It does not find phone numbers that start with 415.

SELECT Phone
   FROM Contacts
   WHERE Phone = '415%';

See also String literals.

Using LIKE with date and time values

You can use LIKE on date and time fields as well as on character data. When you use LIKE with date and time values, the dates are converted to the standard DATETIME format, and then to VARCHAR.

One feature of using LIKE when searching for DATETIME values is that, since date and time entries may contain a variety of date parts, an equality test has to be written carefully to succeed.

For example, if you insert the value 9:20 and the current date into a column named arrival_time, the following clause fails to find the value, because the entry holds the date as well as the time:

WHERE arrival_time = '9:20'

However, the clause below would find the 9:20 value:

WHERE arrival_time LIKE '%09:20%'

Using NOT LIKE

With NOT LIKE, you can use the same wildcard characters that you can use with LIKE. To find all the phone numbers in the Contacts table that do not have 415 as the area code, you can use either of these queries:

SELECT Phone
   FROM Contacts
   WHERE Phone NOT LIKE '415%';

SELECT Phone
   FROM Contacts
   WHERE NOT Phone LIKE '415%';

Using underscores

Another special character that can be used with LIKE is the _ (underscore) character, which matches exactly one character. For example, the pattern 'BR_U%' matches all names starting with BR and having U as the fourth letter. In Braun the _ character matches the letter A and the % matches N. See LIKE search condition.