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