SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-11
Comparing Character Values
This query selects employee numbers and job codes for all employees whose names
are between CLARK, LARRY and FOLEY, MARK:
>> SELECT EMPNUM, JOBCODE
+> FROM EMPLOYEE
+> WHERE LAST_NAME, FIRST_NAME BETWEEN 'CLARK', 'LARRY'
+> AND 'FOLEY', 'MARK';
The names CLARK, JUNE and FOLEY, MAVA would not be selected.
This query selects a customer located at 2300 BROWN BLVD in FRESNO,
CALIFORNIA:
>> SELECT CUSTNUM, CUSTNAME
+> FROM CUSTOMER
+> WHERE STREET = '2300 BROWN BLVD' AND
+> CITY = 'FRESNO' AND
+> STATE = 'CALIFORNIA';
To simplify this query, enter:
>> SELECT CUSTNUM, CUSTNAME
+> FROM CUSTOMER
+> WHERE STREET, CITY, STATE =
+> '2300 BROWN BLVD', 'FRESNO', 'CALIFORNIA';
Comparing Character Values
If a column contains text (character data), you must enclose the comparison value in
single quotation marks.The characters you enter must match exactly as the characters
are stored in the column.
For example, to find a value that consists of uppercase letters, enter uppercase letters.
To select without regard to whether a character value is in lowercase or uppercase,
use the UPSHIFT function.
You must also include the same number of spaces within the comparison string as
there are spaces stored within the column value.
This predicate does not select ’PC Diamond 60 MB’ or ’PC DIAMOND, 60MB’:
+> AND PARTDESC = 'PC DIAMOND, 60 MB';
This predicate selects ’PC Diamond, 60 MB’ and ’PC DIAMOND, 60 MB’ but not ’PC
DIAMOND, 60MB’ (because of the missing space):
+> AND UPSHIFT (PARTDESC) = 'PC DIAMOND, 60 MB';
Note. To compare character data that uses collations, see the COMPARISON entry in the
SQL/MX Reference Manual
Note. The UPSHIFT function upshifts single-byte characters, such as those in ISO88591 and
UCS2 character sets. You cannot use the UPSHIFT function if the column contains a double-
byte character set.