SQL in a Nutshell

Name

LIKE Operator

Synopsis

The LIKE operator enables specified string patterns in SELECT , INSERT, UPDATE, and DELETE statements to be matched. The specified pattern can even include special wildcard characters.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations

SQL99 Syntax and Description

WHERE expression [NOT] LIKE string_pattern

The usefulness of LIKE is based on the wildcard operators that it supports. LIKE returns a TRUE Boolean value when the comparison finds one or more matching values. Note that the default case sensitivity of the DBMS is very important to the behavior of LIKE. For example, Microsoft SQL Server is not case-sensitive by default (though it can be configured that way). So the query:

SELECT *
FROM authors
WHERE lname LIKE 'LARS%'

would find authors whose last names are stored as `larson’ or `lars,’ even though the search was for uppercase `LARS%'. Oracle is case-sensitive to "%" and "_" pattern characters, and has other regular-expression pattern matching available using operators other than LIKE. The wildcard operators are as follows in Table 3.3.

Table 3-3. Wildcard Operators and Sample Code

Wildcard Operator	Example	Description
%	Retrieves any record of city with “ville” in its name. (Supported by all vendors.) SELECT * FROM authors WHERE city LIKE '%ville%'	Matches any string; resembles * in DOS operations.
[ ]	Retrieves any author ...