SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-33
LIKE Predicate
SQL transforms this kind of predicate into a range predicate, as follows:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY >= 50000 AND SALARY <= 72000 ;
The query returns this result:
LAST_NAME FIRST_NAME SALARY
--------- ---------- --------
Simpson Travis 68000.00
Nakagawa Etsuro 72000.00
Nakamura Eichiro 50000.00
Smithson Richard 58300.00
--- 4 row(s) selected.
If the BETWEEN predicate compares two character expressions, these guidelines
apply to the use of character sets:
•
The same character set must be associated with each of the two character
expressions. Any character data type is compatible with other character data types
as long as both have the same associated character set.
•
If neither character expression references a column with an associated collation, a
binary comparison is used for all comparisons.
•
If the character expressions have two different lengths, the shorter string is filled on
the right with spaces to match the length of the longer string. Spaces are used
whether or not a single-byte or double-byte character set is associated with the
expression.
LIKE Predicate
A LIKE predicate searches for character strings that match a pattern. You can use
LIKE to find a character string within a column. For example, you might want to find the
string “Ann” in the VARCHAR column FIRST_NAME.
SELECT FIRST_NAME FROM NAMES
WHERE FIRST_NAME LIKE "Ann" ;
SQL retrieves all values in the column FIRST_NAME that contain the string “Ann” and
contain no trailing blanks.
LIKE performs differently on CHARACTER and VARCHAR columns.