Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443