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

Get SQL in a Nutshell, 2nd Edition 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.