NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-3
Considerations—LIKE
•
If you specify NOT, the predicate is true if the value you are comparing does not
match any string to which you compare it, or is not the same length as any string to
which you compare it. For example, NAME NOT LIKE “_Z” is true if the string is
not two characters long or the last character is not Z.
In a search-condition, the predicate NAME NOT LIKE “ABC” is equivalent
to NOT (NAME LIKE “ABC”).
•
Wild-card characters
You can look for similar values by specifying only a few characters and using the
following wild-card characters:
You must specify the wild-card characters (underscore and percent sign) in the
character set associated with the column or unexpected results can occur. These are
the values for the character sets SQL supports:
•
Escape characters
If you want to search for a string containing a percent sign or underscore, you can
define an escape character (using ESCAPE character) to turn off the special
meaning of percent sign and underscore.
You can specify a character as a host variable, string literal, or a parameter name for
which you supply a one-character value later (for example, ?ESC). If you want to
include a percent sign or underscore in the comparison string, enter the escape
character immediately preceding it. For example, to locate the value A_B, enter:
NAME LIKE "A\_B" ESCAPE "\"
To include the escape character itself in the comparison string, enter two escape
characters. For example, to locate A_B\C%, enter:
NAME LIKE "A\_B\\C\%" ESCAPE "\"
The escape character must precede only the percent sign, underscore, or escape
character itself. For example, if the escape character is \, RA\BS is not valid.
•
CHARACTER columns
% Percent sign indicates zero or more characters of any type are acceptable. For
example, “%ART%” matches “SMART”, “ARTIFICIAL”, and
“PARTICULAR”, but not “smart”.
_ Underscore indicates any single character is acceptable. For example, “BOO_”
matches “BOOK” or “BOOR”; but not “BOO”, “BOOKLET”, or “book.”
Character Set Underscore Percent Sign
UNKNOWN HEX 5F HEX 25
ISO99591/9 HEX 5F HEX 25
KANJI HEX8151 HEX 8193
KSC5601 HEX A3DF HEX A3A5