O'Reilly logo

SQL in a Nutshell, 3rd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

LIKE Operator

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

MySQL

Supported

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 returns 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 the CHAR, VARCHAR, and DATE datatypes.

  • Using LIKE may negate indexes ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required