SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-12
Developing a Query
The following rules describe how the system compares character values in comparison
and LIKE predicates. (BETWEEN and IN predicates follow the same rules as
comparison predicates.)
•
Trailing blanks are significant for fixed-length columns. For example, the value
"DISK" inserted in a CHAR(6) column is "DISK".
•
Only the data inserted is significant for variable-length columns. For example, the
value "DISK" inserted in a VARCHAR(6) column is "DISK".
•
When processing comparison predicates, the system pads the comparison value
or the column value (whichever is smaller) to make the values the same length.
•
When processing LIKE predicates, the system does not pad the column value with
blanks.
•
Unless you specify a % wild-card character in the comparison value, the condition
is met only if the column value and the comparison value are the same length.
Table 3-2 provides some examples to illustrate these rules.
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)