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 ... |
Get SQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.