LIKE predicate

A LIKE predicate determines whether a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 expression contains a given pattern. The predicate evaluates to TRUE if an expression contains the pattern.

SQL syntax

Expression [NOT] LIKE
 {'PatternString'| {? | :DynamicParameter}} 
[ESCAPE {'EscapeChar' | {? | :DynamicParameter}}]

Parameters

Parameter	Description
`Expression`	The syntax of expressions is presented in Chapter 3, "Expressions".
`PatternString`	Describes what you are searching for in the expression. The pattern may consist of characters only (including digits and special characters). For example, `NAME LIKE 'Annie'` evaluates to `TRUE` only for a name of Annie with no spaces. Upper case and lower case are significant. You can also use the predicate to test for a partial match by using the following symbols in the pattern: _ Represents any single character. For example, `BOB` and `TOM` both satisfy the predicate `NAME LIKE '_O_'`. % Represents any string of zero or more characters. For example, `MARIE` and `RENATE` both satisfy the predicate `NAME LIKE '%A%'`. You can use the `_` and % symbols multiple times and in any combination in a pattern. You cannot use these symbols literally within a pattern unless you use the ESCAPE clause and precede the symbols with the escape character, described by the `EscapeChar` parameter.
`EscapeChar`	Describes an optional escape character which can be used to interpret the symbols _ and % literally in the pattern. The escape character must be a single character. When it appears in the pattern, it must be followed by the escape character itself, the _ symbol or the % symbol. Each such pair represents a single literal occurrence of the second character in the pattern. The escape character is always case sensitive. The escape character cannot be `_` or %.
? `DynamicParameter`	Indicates a dynamic parameter in a prepared SQL statement. The parameter value is supplied when the statement is executed.

Description

As long as no escape character is specified, the _ or % in the pattern acts as a wild card character. If an escape character is specified, then the wild card or escape character that follows is treated literally. If the character following an escape character is not a wild card or the escape character, an error results.
If the value of the expression, the pattern, or the escape character is NULL, then the LIKE predicate evaluates to NULL and the row is not operated on.

Examples

Vendors located in states beginning with an "A" are identified.

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorState LIKE 'A%';

Vendors whose names begin with ACME_ are identified (note use of the ESCAPE clause).

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorName LIKE 'ACME!_%' ESCAPE '!';

NCHAR and NVARCHAR2

The LIKE predicate can be used for pattern matching of NCHAR and NVARCHAR2 strings. The pattern matching characters are:

Character	Description
U+005F SPACING UNDERSCORE	Represents any single Unicode character.
U+0025 PERCENT SIGN	Represents any string of zero or more Unicode characters.

Description

The escape character is similarly supported as a single Unicode character or parameter.
The types of the LIKE operands can be any combination of character types.
Case- and accent-insensitive NLS_SORT is supported with the LIKE predicate.

Examples

In these examples, the Unicode character U+0021 EXCLAMATION MARK is being used to escape the Unicode character U+005F SPACING UNDERSCORE. Unicode character U+0025 PERCENT SIGN is not escaped, and assumes its pattern matching meaning.

VendorName is an NCHAR or NVARCHAR2 column.

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorName LIKE N'ACME!_%' ESCAPE N'!';

This example is equivalent:

SELECT VendorName FROM Purchasing.Vendors
WHERE VendorName LIKE N'ACME!\u005F\u0025' ESCAPE N'!';