SQL in a Nutshell, 2nd Edition

Name

LIKE Operator

Synopsis

The LIKE operator enables specified string patterns in SELECT, INSERT, UPDATE, and DELETE statements to be matched, specifically in the WHERE clause. A specified pattern may include special wildcard characters. The specific wildcards supported vary from platform to platform.

Platform	Command
DB2	Supported
MySQL	Supported, with variations
Oracle	Supported
PostgreSQL	Supported, with variations
SQL Server	Supported, with variations

SQL2003 Syntax

WHERE expression [NOT] LIKE string_pattern
   [ESCAPE escape_sequence]

Keywords

WHERE expression LIKE

Returns a Boolean TRUE when the value of expression matches the string_pattern. The expression may be a column, a constant, a host variable, a scalar function, or a concatenation of any of these. It should not be a user-defined type, nor should it be certain types of LOBs.

NOT

Inverses the predictate. The statement returns a Boolean TRUE if the value of expression does not contain the string_pattern, and FALSE if the value of expression contains the string_pattern.

ESCAPE escape_sequence

Allows you to search for the presence of characters that would normally be interpreted as wildcards.

Rules at a Glance

Matching string patterns is easy with LIKE, but there are a couple of simple rules to remember:

• All characters, including trailing and leading spaces, are important.

• Differing datatypes may be compared using LIKE, but they store string patterns differently. In particular, be aware of the differences between CHAR, ...