Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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

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

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'!';