IBM SC41-5210-04 manual Lastname Like, ’Anne Eva’

You can use some special characters to represent the positions in the field that you do not care about:

vAn 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.

vA 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:

vThe field length is 1, you could specify ‘A’

vThe field length is 3, you could specify ‘A__’

vThe 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