SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-94
LIKE Predicate
Wild Card Characters
You can look for similar values by specifying only part of the characters of pattern
combined with these wild-card characters:
Escape Characters
To search for a string containing a percent sign or underscore, define an escape
character (using ESCAPE esc-char-expression) to turn off the special meaning of
percent sign and underscore.
To include a percent sign or underscore in the comparison string, type the escape
character immediately preceding it. For example, to locate the value 'A_B', type:
NAME LIKE 'A\_B' ESCAPE '\'
To include the escape character itself in the comparison string, type two escape
characters. For example, to locate 'A_B\C%', type:
NAME LIKE 'A\_B\\C\%' ESCAPE '\'
The escape character must precede only the percent sign, underscore, or escape
character itself. For example, the pattern RA\BS is not valid if the escape character is
defined to be '\'.
Comparing the Pattern to CHAR Columns
Columns of data type CHAR are fixed length. When a value is inserted into a CHAR
column, NonStop SQL/MX pads the value in the column with blanks if necessary. The
value 'JOE' inserted into a CHAR(6) column becomes 'JOE ' (3 characters plus 3
blanks). The LIKE predicate is true only if the column value and the comparison value
are the same length. The column value 'JOE ' does not match 'JOE' but does match
'JOE%'.
Comparing the Pattern to VARCHAR Columns
Columns of variable-length character data types do not include trailing blanks unless
blanks are specified when data is entered. For example, the value 'JOE' inserted in a
VARCHAR(4) column is 'JOE' (with no trailing blanks). The value matches both 'JOE'
and 'JOE%'.
If you cannot locate a value in a variable-length character column, it might be because
trailing blanks were specified when the value was inserted into the table. For example,
a value of '5MB ' (with 1 trailing blank) will not be located by LIKE '%MB' but will be
located by '%MB%'.
% Use a percent sign to indicate zero or more characters of any type. For example,
'%ART%' matches 'SMART', 'ARTIFICIAL', and 'PARTICULAR'—but not
'smart'. The code value for %r for KANJI character set is 0x8193, while that for
KSC5601 is 0xA3A5.
_ Use an underscore to indicate any single character. For example, 'BOO_'
matches 'BOOK' or 'BOOR'—but not 'BOO', 'BOOKLET', or 'book'. The code
value for _ for KANJI character set is 0x8151, while that for KSC5601 is 0xA3DF.