NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-4
Considerations—LIKE
The following 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 “ will not match “JOE”
but will match “JOE%”.
°
The TERMINATE clause is useful in host programs where the pattern appears in
a host variable of data type CHARACTER. The following example finds all
names that contain a y:
MOVE "%y%@" TO hostvar
WHERE NAME LIKE :hostvar TERMINATE "@"
•
Varying-length character columns
The following 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__” will not be located by LIKE “%MB,”
but will be located 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 the following values in the EMPLOYEE table:
NAME
----
Jay
Mike
Holly
Dave