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