SQL Reference Guide

LIKE Predicate

Description

The LIKE predicate searches for strings that have a certain pattern. The pattern is specified after the LIKE keyword in a scalar expression which can be as simple as a string constant or a complex expression containing concatenation operators, scalar functions, etc. The pattern can be specified by a string in which the underscore ( _ ) and percent sign ( % ) characters have special semantics.

This predicate can be used inside of a WHERE, HAVING, or JOIN statement.

The ESCAPE clause can be used to disable the special semantics given to characters ‘ _ ’ and ‘ % ’. The escape character specified must precede the special characters to disable their special semantics.

Syntax

like_predicate ::

column_name [ NOT ] LIKE expr

[ ESCAPE escape-character ]

Notes

The column name specified in the LIKE predicate must refer to a character string column.
A percent sign in the pattern matches zero or more characters of the column string.
A underscore sign in the pattern matches any single character of the column string.

Examples

cust_name LIKE '%Computer%'

cust_name LIKE '___'

item_name LIKE '%\_%' ESCAPE '\'

item_name LIKE left(part_name, 5) + '%'

In the first example, for all strings with the substring Computer, the predicate will evaluate to true. In the second example, for all strings which are exactly three characters long, the predicate will evaluate to true. In the third example, the backslash character ‘ \ ’ has been specified as the escape character, which means that the special interpretation given to the character ‘ _ ’ is disabled. The pattern will evaluate to TRUE if the column item_name has embedded underscore characters.

In the fourth example, the LIKE predicate evaluates to true if the first 5 characters of item_name match the first 5 characters of part_name.