IBM SC41-5210-04 manual Query for iSeries character functions, Substr Query for iSeries function

For example, if a character constant ’Dr. ’ and a character field named LASTNAME containing the value Smith are concatenated, the result is a field containing the value Dr. Smith. Other examples of character expressions are:

NAME ’Mr.’

’Mr.’ NAME FIRSTINIT MIDINIT

If any field is null-capable, the resulting field is null-capable. If any field used in a concatenation is null, the resulting expression is null.

Except for the case of two DBCS-open fields, if all concatenated values are either fixed-length fields or constants, the result is a fixed-length field. The concatenation of two DBCS-open fields results in a DBCS-open field that allows for the varying lengths that can result from concatenating DBCS-open fields. If any field is variable-length, the result is a variable-length field.

A UCS2-graphic field can only be concatenated with another UCS2-graphic field.

The CCSID of the result is as follows:

vCCSID 65535 if any field or constant has CCSID 65535.

vMixed CCSID if any field has a mixed CCSID.

vCCSID of a file field has precedence over result fields or constants.

vCCSID of result fields has precedence over constants.

vCCSID assigned to the result will be the CCSID of the first of the two values if both concatenated values are fields from the file, or both are result fields.

vCCSID 65535 is assigned to a DBCS-graphic constant if the job CCSID is a single-byte CCSID with no associated DBCS CCSID.

Query for iSeries character functions

Character expressions can include the character functions SUBSTR, DIGITS, and VALUE.

SUBSTR Query for iSeries function

The SUBSTR function returns part of a character value. The form is:

SUBSTR ( value , offset	)
	,length

vValue is the name of a character field (including result fields that are already defined), a character expression, or a character constant. It can be an SBCS or DBCS field, an SBCS constant, a DBCS-open constant, a DBCS-only constant, or a DBCS-graphic constant. A SUBSTR operation on a DBCS-only or DBCS-either field produces an SBCS character data type. A SUBSTR operation on a DBCS-open field produces a DBCS-open data type. A SUBSTR operation on a DBCS-graphic field produces a DBCS-graphic data type. If Value is a field, the CCSID of the result is the CCSID of the field. If Value is a constant, the CCSID of the result is the associated CCSID of the job of the user who creates the query (or of the user who changes the query, if the original CCSID was 65535).

vOffset is the starting character position within the field or character constant. An expression can also be used for the offset.

vLength is the number of characters that make up the substring. An expression may be used for length. You do not need to specify a length. If you do not, the resulting substring is the entire field or character constant from Offset to the end.

You must use commas between the substring elements; blanks are allowed after the commas. If you are sending queries between countries that use the comma for a decimal point, put a blank after the comma separating the offset and length.

Chapter 5. Defining result fields in Query for iSeries 61