SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-13
Comparing Character Values
Because trailing blanks are significant for the LIKE predicate, it might be more
restrictive than a comparison predicate when comparing columns of fixed-length
character data types.
If you cannot locate a value in a column of a variable-length character data type, it
might be because trailing blanks were included when the value was inserted in the
table.
For example, a value of ’5MB ’ is not located by LIKE ’%MB’. Use comparison
predicates to search for exact values, and try including a percent sign (%) at the end of
the comparison value.
Table 3-2. Comparison and LIKE Predicates
Column Definition
Value
Inserted
Stored
Value Predicate Result
Fixed-length:
CHAR (6)
PIC X (6)
NATIONAL CHAR
(3)
NCHAR (6)
‘5MB’
‘120^MB’
‘5MB^^^’
‘120^MB’
LIKE “%MB”
LIKE ‘%MB%’
= ‘5MB’
LIKE ‘%MB’
Fails, last 2
characters not MB
Succeeds
Succeeds
Succeeds, last 2
characters are MB
Variable-length
CHAR VARYING (6)
VARCHAR (6)
NATIONAL CHAR
VARYING (3)
NCHAR VARYING
(3)
‘5MB’
‘120^MB’
‘5MB’
‘120^MB’
LIKE ‘%MB’
= ‘5MB’
LIKE ‘5MB’
LIKE “5MB%”
LIKE ‘_MB’
LIKE ‘_MB%’
LIKE ‘%MB’
Succeeds
Succeeds
Succeeds
Succeeds
Succeeds
Succeeds
Succeeds
^ indicates a space (blank character)
Note.
Specifying a pattern beginning with percent (%) in a LIKE predicate can result in a scan of a
complete table. You should not use this type of pattern when executing a query online unless other
predicates in the query provide access paths through indexes or primary keys. If you need to use this
type of pattern, execute the query in batch mode when the system has fewer demands on its resources.