IBM @server iSeries, User Manual

Page: 111 / 294

You can use some special characters to represent the positions in the field that you do not care about:
v
An underscore (_) means skip one character at that position and do not test for that character. Each
underscore takes the place of one character in the field.
v
A percent sign (%) means skip as many characters as necessary to get to the end of the field or to the
next specified character. Each % takes the place of zero or more characters in the field. (Thus, you
need not write a test pattern that is 32 characters long just because the field is 32 characters long.)
A test pattern must be no longer than, but it can be as long as, the actual field. Query does not count a %
as a character and counts two apostrophes within a constant as one character when checking pattern
length.
For example, if you are testing for an
A
in the first position of the
LASTNAME
field, and if:
v
The field length is 1, you could specify
‘A’
v
The field length is 3, you could specify
‘A__’
v
The field length is at least 1, you could specify
‘A%’
You would type them on the display as follows:
Select Records
Type comparisons, press Enter. Specify OR to start each new group.
Tests: EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...
AND/OR Field Test Value (Field, Number, or ’Characters’)
LASTNAME LIKE ’A%’
____ ______________ _____ ______________________________
____ ______________ _____ ______________________________
If you are testing for an
A
in the last position of the field, you could specify
‘%A’
. But note that if an
8-character field contained
’ANNA ’
, the LIKE test would fail because the last position in the field is a
blank.
If you are testing for an
E
followed by an
A
two positions after it, and the field length is at least 3, you
could specify:
’%E_A%’
Note that if an 8-character field contained any of the following, the LIKE test would be true:
’ANNE A. ’ ’EVAN E. ’ ’ANNE EVA’
Note: A LIKE, NLIKE test cannot be used for a date, time, or timestamp field. Only character constants
are allowed for LIKE, NLIKE. To circumvent this, create a result field and use the CHAR function on
the date, time, or timestamp field. Then use the result field on the Select Records display.
Testing for DBCS LIKE (like) and NLIKE (not like) in Query for iSeries
DBCS LIKE or NLIKE tests can be used in the Value column of the Select Records display to select
records. The following types of LIKE patterns can be used for DBCS fields that are being tested: standard
SBCS character constants, DBCS-only character constants, DBCS-open character constants, and
DBCS-graphic character constants.
Non-DBCS character LIKE, NLIKE (not like) pattern in Query for iSeries: This pattern, which contains
no DBCS data, can be used to test DBCS-either and DBCS-open fields. Use a % sign to skip any number
of characters or none at all. Use an underscore (_) to represent any single SBCS character. Although the
LIKE pattern contains only SBCS characters, it can test DBCS-open fields that contain both DBCS data
and SBCS data, because a % sign can be used to skip over both kinds of data.
Chapter 7. Selecting records in Query for iSeries
99