HP 3000 Manuals HP 3000 Manuals
LIKE Predicate
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
{'PatternString' }
{:HostVariable1 [[INDICATOR] :IndicatorVariable1]}
Expression [NOT] LIKE {? }
{:LocalVariable1 }
{:ProcedureParameter1 }
[ {'EscapeChar' }]
[ {:HostVariable2 [[INDICATOR]:IndicatorVariable2]}]
[ESCAPE {? }]
[ {: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 '!'