SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
L-4
Considerations—LIKE
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.

These guidelines apply to pattern matching using columns of data type
CHARACTER:

Columns of data type CHARACTER are fixed length.
When a value is entered, SQL pads the value in the column with blanks if
necessary. The value “JOE” inserted in a CHAR(6) column becomes “JOE “
(3 characters plus 3 blanks).
In a comparison value, the condition is met only if the column value and the
comparison value are the same length. The value “JOE “ does not match
“JOE” but matches “JOE%”.

The TERMINATE clause is useful in host programs where the pattern appears
in a host variable of data type CHARACTER. This example finds all names that
contain a y:
MOVE "%y%@" TO hostvar
WHERE NAME LIKE :hostvar TERMINATE "@"

These guidelines apply to pattern matching using columns of varying-length
character data types:

Columns of varying-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”. The value matches both
“JOE” and “JOE%.”

If you cannot locate a value in a varying-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__” is not located by LIKE “%MB,” but by
“%MB%.”

The TERMINATE clause specifies the end of a pattern within a pattern-
matching string. For example, column NAME, defined as a VARCHAR column,
contains these values in the EMPLOYEE table:
NAME
----
Jay
Mike
Holly
Dave