Filter
Top Products
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:
v CCSID 65535 if any field or constant has CCSID 65535.
v Mixed CCSID if any field has a mixed CCSID.
v CCSID of a file field has precedence over result fields or constants.
v CCSID of result fields has precedence over constants.
v CCSID 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.
v CCSID 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
v Value 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).
v Offset is the starting character position within the field or character constant. An expression can also be
used for the offset.
v Length 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