 |
» |
|
|
|
A LIKE predicate determines whether an expression contains
a given pattern. The predicate evaluates to TRUE if an
expression contains the pattern. If the NOT option is
used, the predicate evaluates to TRUE if the expression
does not contain the pattern. Scope | |
SQL Data Manipulation Statements
SQL Syntax | |
Expression [NOT] LIKE { 'PatternString' :HostVariable1 [ [INDICATOR] :IndicatorVariable1] ? :LocalVariable1 :ProcedureParameter1 } [ESCAPE
{ 'EscapeChar' :HostVariable2 [[INDICATOR]:IndicatorVariable2] ? :LocalVariable2 :ProcedureParameter2 } ] Parameters | |
- Expression
specifies a value used to identify columns,
screen rows, or define new column values.
The syntax of expressions is presented in
the "Expressions" chapter. Only CHAR and VARCHAR
expressions are
valid in LIKE predicates. Date/time columns cannot
be referred to directly; however, they can be
placed inside the conversion function TO_CHAR and be converted
to a CHAR value. Expression cannot be a subquery.
- NOT
reverses the value of the predicate.
- PatternString
describes what you are searching for in the expression.
The pattern can consist of characters only (including digits).
For example, NAME LIKE 'Annie' evaluates to
true only for a name of Annie.
Uppercase and lowercase are significant.
You can also use the predicate to test for
the existence of 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, THOMAS and TOM both satisfy the predicate
NAME LIKE '%O%'.
The _ and % symbols can be used multiple
times and in any combination in a pattern.
You cannot use these symbols literally within a pattern unless the ESCAPE
clause appears, and the escape character precedes them.
Note that they must be ASCII and not your local representations.
- HostVariable1
identifies the host variable in which the pattern is stored. - IndicatorVariable1
names an indicator variable, an input host variable whose value
determines whether the associated host variable contains a NULL value:
- >= 0
the value is not NULL
- < 0
the value is NULL
- EscapeChar
describes an optional escape character which can be
used to include the symbols _ and % in the pattern. The escape character must be a single character, although it can be a one-
or two-byte NLS character. When it appears in the pattern, it must be
followed by the escaped character, host variable or, _, or %. Each such pair represents a
single literal occurrence of the second character in the pattern.
The escape character is always case sensitive.
All other characters are interpreted as described before.
- HostVariable2
identifies the host variable containing the escape character. - IndicatorVariable2
names an indicator variable, an input host variable whose value
determines whether the associated host variable contains a NULL value: - >=0
the value is not NULL
- < 0
the value is NULL
If the escape character is NULL, the predicate evaluates to unknown.
- LocalVariable2
contains the escape character.
- ProcedureParameter2
contains the escape character that is passed into or out of a
procedure.
- ?
indicates a dynamic parameter in a prepared SQL statement. The
value of the parameter is supplied when the statement is executed.
Description | |
If an escape character is not specified, then the _ or % in the pattern
continues to act as a wildcard. No default escape character is available.
If an escape character is specified, then the wildcard or escape
character which follows an escape character is treated as a constant.
If the character following an escape character is not a wildcard 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 unknown.
Example | |
Vendors located in states beginning with an A are identified.
SELECT VendorName FROM PurchDB.Vendors
WHERE VendorState LIKE 'A%'
|
Vendors whose names begin with ACME_ are identified.
SELECT VendorName FROM PurchDB.Vendors
WHERE VendorName LIKE 'ACME!_%' ESCAPE '!'
|
|
