GRAPHIC

The GRAPHIC function returns a fixed-length graphic-string representation of a string expression.

Integer to Graphic

GRAPHIC(integer-expression)

Decimal to GRAPHIC

GRAPHIC(decimal-expression,decimal-character)

Floating-point to GRAPHIC

GRAPHIC(floating-point-expression,decimal-character)

Decimal floating-point to GRAPHIC

GRAPHIC(decimal-floating-point-expression,decimal-character)

Character to Graphic

GRAPHIC(character-expression,lengthDEFAULT,integer)

Graphic to Graphic

GRAPHIC(graphic-expression,lengthDEFAULT,integer)

Datetime to Graphic

GRAPHIC(datetime-expression,ISOUSAEURJISLOCAL)

The GRAPHIC function returns a graphic-string representation of:

  • An integer number if the first argument is a SMALLINT, INTEGER, or BIGINT
  • A decimal number if the first argument is a packed or zoned decimal number
  • A double-precision floating-point number if the first argument is a DOUBLE or REAL
  • A decimal floating-point number if the first argument is a DECFLOAT
  • A character string if the first argument is any type of character string
  • A graphic string if the first argument is any type of graphic string
  • A date value if the first argument is a DATE.
  • A time value if the first argument is a TIME.
  • A timestamp value if the first argument is a TIMESTAMP.

The result of the function is a fixed-length graphic string (GRAPHIC).

If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.

Integer to Graphic

integer-expression
An expression that returns a value that is an integer data type (either SMALLINT, INTEGER, or BIGINT).

The result is a fixed-length graphic string of the argument in the form of an SQL integer constant. The result consists of n characters that are the significant digits that represent the value of the argument with a preceding minus sign if the argument is negative. It is left justified.

  • If the argument is a small integer, the length attribute of the result is 6.
  • If the argument is a large integer, the length attribute of the result is 11.
  • If the argument is a big integer, the length attribute of the result is 20.

The result is the smallest number of characters that can be used to represent the value of the argument. Leading zeroes are not included. If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit or the decimal-character.

The CCSID of the result is 1200 (UTF-16).

Decimal to Graphic

decimal-expression
An expression that returns a value that is a packed or zoned decimal data type (either DECIMAL or NUMERIC). If a different precision and scale is wanted, the DECIMAL scalar function can be used to make the change.
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character must be a period or comma. If the second argument is not specified, the decimal point is the default decimal point. For more information, see Decimal point.

The result is a fixed-length graphic string representation of the argument. The result includes a decimal character and up to p digits, where p is the precision of the decimal-expression with a preceding minus sign if the argument is negative. Leading zeros are not returned. Trailing zeros are returned. If the scale of decimal-expression is zero, the decimal character is not returned.

The length attribute of the result is 2+p where p is the precision of the decimal-expression. The result is the smallest number of characters that can be used to represent the result. Leading zeros are not included. If the argument is negative, the result begins with a minus sign. Otherwise, the result begins with a digit or the decimal-character.

The CCSID of the result is 1200 (UTF-16).

Floating-point to Graphic

floating-point expression
An expression that returns a value that is a floating-point data type (DOUBLE or REAL).
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character must be a period or comma. If the second argument is not specified, the decimal point is the default decimal point. For more information, see Decimal point.

The result is a fixed-length graphic string representation of the argument in the form of a floating-point constant.

The length attribute of the result is 24. The result is the smallest number of characters that can represent the value of the argument such that the mantissa consists of a single digit other than zero followed by the decimal-character and a sequence of digits. If the argument is negative, the first character of the result is a minus sign; otherwise, the first character is a digit or the decimal-character. If the argument is zero, the result is 0E0.

The CCSID of the result is 1200 (UTF-16).

Decimal floating-point to Graphic

decimal-floating-point expression
An expression that returns a value that is a built-in decimal floating-point data type.
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character must be a period or comma. If the second argument is not specified, the decimal point is the default decimal point. For more information, see Decimal point.

The result is a fixed-length graphic string representation of the argument in the form of a floating-point constant.

The length attribute of the result is 42. The actual length of the result is the smallest number of characters that represents the value of the argument, including the sign, digits, and decimal-character. Trailing zeros are significant. If the argument is negative, the first character of the result is a minus sign; otherwise, the first character is a digit or the decimal-character. If the argument is zero, the result is 0.

If the DECFLOAT value is Infinity, sNaN, or NaN, the strings 'INFINITY', 'SNAN', and 'NAN', respectively, are returned. If the special value is negative, a minus sign will be the first character in the string. The DECFLOAT special value sNaN does not result in an exception when converted to a string.

The CCSID of the result is 1200 (UTF-16).

Character to Graphic

character-expression
An expression that returns a value that is a built-in character-string data type. It cannot be a CHAR or VARCHAR bit data. If the expression is an empty string or the EBCDIC string X'0E0F', the result is a single double-byte blank.
length
An integer constant that specifies the length attribute of the result and must be an integer constant between 1 and 16383 if the first argument is not nullable or between 1 and 16382 if the first argument is nullable. If the length of character-expression is less than the length specified, the result is padded with double-byte blanks to the length of the result.

If length is not specified, or if DEFAULT is specified, the length attribute of the result is the same as the length attribute of the first argument.

Each character of the argument determines a character of the result. If the length attribute of the resulting fixed-length string is less than the actual length of the first argument, truncation is performed and no warning is returned.

integer
An integer constant that specifies the CCSID of the result. It must be a DBCS, UTF-16, or UCS-2 CCSID. The CCSID cannot be 65535. If the CCSID represents Unicode graphic data, each character of the argument determines a character of the result. The nth character of the result is the UTF-16 or UCS-2 equivalent of the nth character of the argument.

If integer is not specified then the CCSID of the result is determined by a mixed CCSID. Let M denote that mixed CCSID.

In the following rules, S denotes one of the following:

  • If the string expression is a host variable containing data in a foreign encoding scheme, S is the result of the expression after converting the data to a CCSID in a native encoding scheme. (See Character conversion for more information.)
  • If the string expression is data in a native encoding scheme, S is that string expression.

M is determined as follows:

  • If the CCSID of S is a mixed CCSID, M is that CCSID.
  • If the CCSID of S is an SBCS CCSID:
    • If the CCSID of S has an associated mixed CCSID, M is that CCSID.
    • Otherwise the operation is not allowed.

The following table summarizes the result CCSID based on M.

M Result CCSID Description DBCS Substitution Character
930 300 Japanese EBCDIC X'FEFE'
933 834 Korean EBCDIC X'FEFE'
935 837 S-Chinese EBCDIC X'FEFE'
937 835 T-Chinese EBCDIC X'FEFE'
939 300 Japanese EBCDIC X'FEFE'
5026 4396 Japanese EBCDIC X'FEFE'
5035 4396 Japanese EBCDIC X'FEFE'

The equivalence of SBCS and DBCS characters depends on M. Regardless of the CCSID, every double-byte code point in the argument is considered a DBCS character, and every single-byte code point in the argument is considered an SBCS character with the exception of the EBCDIC mixed data shift codes X'0E' and X'0F'.

  • If the nth character of the argument is a DBCS character, the nth character of the result is that DBCS character.
  • If the nth character of the argument is an SBCS character that has an equivalent DBCS character, the nth character of the result is that equivalent DBCS character.
  • If the nth character of the argument is an SBCS character that does not have an equivalent DBCS character, the nth character of the result is the DBCS substitution character.

Graphic to Graphic

graphic-expression
An expression that returns a value of a built-in graphic-string data type.
length
An integer constant that specifies the length attribute of the result and must be an integer constant between 1 and 16383 if the first argument is not nullable or between 1 and 16382 if the first argument is nullable. If the length of graphic-expression is less than the length specified, the result is padded with double-byte blanks to the length of the result.

If the second argument is not specified, or if DEFAULT is specified, the length attribute of the result is the same as the length attribute of the first argument.

If the length of the graphic-expression is greater than the length attribute of the result, truncation is performed. A warning (SQLSTATE 01004) is returned unless the truncated characters were all blanks.

integer
An integer constant that specifies the CCSID of the result. It must be a DBCS, UTF-16, or UCS-2 CCSID. The CCSID cannot be 65535.

If integer is not specified then the CCSID of the result is the CCSID of the first argument.

Datetime to Graphic

datetime-expression
An expression that is one of the following three built-in data types
date
The result is the graphic-string representation of the date in the format specified by the second argument. If the second argument is not specified, the format used is the default date format. If the format is ISO, USA, EUR, or JIS, the length of the result is 10. Otherwise the length of the result is the length of the default date format. For more information see String representations of datetime values.
time
The result is the graphic-string representation of the time in the format specified by the second argument. If the second argument is not specified, the format used is the default time format. The length of the result is 8. For more information see String representations of datetime values.
timestamp
The second argument is not applicable and must not be specified.

The result is the graphic-string representation of the timestamp. If datetime-expression is a TIMESTAMP(0), the length of the result is 19. If the data type of datetime-expression is a TIMESTAMP(n), the length of the result is 20+n. Otherwise, the length of the result is 26.

The CCSID of the result is 1200 (UTF-16).
ISO, EUR, USA, or JIS
Specifies the date or time format of the resulting graphic string. For more information, see String representations of datetime values.
LOCAL
Specifies that the date or time format of the resulting graphic string should come from the DATFMT, DATSEP, TIMFMT, and TIMSEP attributes of the job at the current server.

Note

Syntax alternatives: The CAST specification should be used to increase the portability of applications when the first argument is a string and the length attribute is specified. For more information, see CAST specification.

Example

  • Using the EMPLOYEE table, set the host variable DESC (GRAPHIC(24)) to the GRAPHIC equivalent of the first name (FIRSTNME) for employee number (EMPNO) '000050'.
      SELECT GRAPHIC( VARGRAPHIC(FIRSTNME))
        INTO :DESC
        FROM EMPLOYEE
        WHERE EMPNO = '000050'