O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required