GRAPHIC scalar function

Integer to graphic:

Decimal to graphic:

Floating-point to graphic:

Decimal floating-point to graphic:

Character to graphic:

Graphic to graphic:

Datetime to graphic:

The schema is SYSIBM. The function name cannot be specified as a qualified name when keywords are used in the function signature.

The result of the function is a fixed-length graphic string. 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 of an integer data type (SMALLINT, INTEGER, or BIGINT).

The result is a fixed-length graphic string representation of integer-expression in the form of an SQL integer constant. The result consists of n double-byte characters, which represent the significant digits in the argument, and is preceded by a minus sign if the argument is negative. The result is left-aligned.

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

If the number of double-byte characters in the result is less than the defined length of the result, the result is padded on the right with blanks.

The code page of the result is the DBCS code page of the section.

Decimal to graphic

decimal-expression

An expression that returns a value that is a decimal data type. The DECIMAL scalar function can be used to change the precision and scale.

decimal-character

Specifies the double-byte character constant that is used to delimit the decimal digits in the result graphic string. The double-byte character constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a fixed-length graphic string representation of decimal-expression in the form of an SQL decimal constant. The length of the result is 2+p, where p is the precision of decimal-expression. Leading zeros are not included. Trailing zeros are included. If decimal-expression is negative, the first double-byte character of the result is a minus sign; otherwise, the first double-byte character is a digit or the decimal character. If the scale of decimal-expression is zero, the decimal character is not returned. If the number of double-byte characters in the result is less than the defined length of the result, the result is padded on the right with blanks.

The code page of the result is the DBCS code page of the section.

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 double-byte character constant that is used to delimit the decimal digits in the result graphic string. The double-byte character constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a fixed-length graphic string representation of floating-point-expression in the form of an SQL floating-point constant. The length of the result is 24. The result is the smallest number of double-byte characters that can represent the value of floating-point-expression such that the mantissa consists of a single digit other than zero followed by a period and a sequence of digits. If floating-point-expression is negative, the first double-byte character of the result is a minus sign; otherwise, the first double-byte character is a digit. If floating-point-expression is zero, the result is 0E0. If the number of double-byte characters in the result is less than 24, the result is padded on the right with blanks.

The code page of the result is the DBCS code page of the section.

Decimal floating-point to graphic

decimal-floating-point-expression

An expression that returns a value that is a decimal floating-point data type (DECFLOAT).

decimal-character

Specifies the double-byte character constant that is used to delimit the decimal digits in the result graphic string. The double-byte character constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a fixed-length graphic string representation of decimal-floating-point-expression in the form of an SQL decimal floating-point constant. The length attribute of the result is 42. The result is the smallest number of double-byte characters that can represent the value of decimal-floating-point-expression. If decimal-floating-point-expression is negative, the first double-byte character of the result is a minus sign; otherwise, the first double-byte character is a digit. If decimal-floating-point-expression is zero, the result is 0.

If the value of decimal-floating-point-expression is the special value Infinity, sNaN, or NaN, the strings G'INFINITY', G'SNAN', and G'NAN', respectively, are returned. If the special value is negative, the first double-byte character of the result is a minus sign. The decimal floating-point special value sNaN does not result in a warning when converted to a string. If the number of double-byte characters in the result is less than 42, the result is padded on the right with blanks.

The code page of the result is the DBCS code page of the section.

Character to graphic

In Unicode databases:

character-expression

An expression that returns a value that is a built-in character string data type. The expression must not be a FOR BIT DATA subtype (SQLSTATE 42846).

integer

An integer constant that specifies the length attribute for the resulting fixed-length graphic string. The value must be between 0 and the maximum length for the GRAPHIC data type in the string units of the result.

If the second argument is not specified:

• If the character-expression is the empty string constant, the length attribute of the result is 0.
• Otherwise, the length attribute of the result is the lower of the following values:
  • The maximum length for the GRAPHIC data type in the string units of the result
  • The length attribute of the first argument

The result is a fixed-length graphic string that is converted from character-expression. The length attribute of the result is determined by the value of integer.

The actual length of the result is the same as the length attribute of the result.

If the length of character-expression that is converted to a graphic string is less than the length attribute of the result, the result is padded with blanks up to the length attribute of the result.

If the length of character-expression that is converted to a graphic string is greater than the length attribute of the result, several scenarios exist:

• If the string unit of the result is CODEUNITS32, truncation is performed. If only blank characters are truncated and character-expression is CHAR or VARCHAR, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004).
• If integer is specified, truncation is performed. If only blank characters are truncated and character-expression is CHAR or VARCHAR, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004). When the output string is truncated, such that the last character is a high surrogate, that surrogate is converted to the blank character (X'0020'). Do not rely on this behavior because it might change in a future release.
• If integer is not specified, an error is returned (SQLSTATE 22001).

For details about the conversion process, see VARGRAPHIC scalar function.

In non-Unicode databases:

character-expression

An expression that returns a value that is a built-in CHAR or VARCHAR data type.

The result is a fixed-length graphic string that is converted from character-expression. The length attribute of the result is the minimum of the length attribute of character-expression and the maximum length for the GRAPHIC data type.

The actual length of the result is the same as the length attribute of the result. If the length of character-expression that is converted to a graphic string is less than the length attribute of the result, the result is padded with blanks up to the length attribute of the result. If the length of character-expression that is converted to a graphic string is greater than the length attribute of the result, an error is returned (SQLSTATE 22001).

For details about the conversion process, see VARGRAPHIC scalar function.

Graphic to graphic

graphic-expression

An expression that returns a built-in value that is a graphic string data type.

integer

An integer constant that specifies the length attribute for the resulting fixed-length graphic string. The value must be between 0 and the maximum length for the GRAPHIC data type in the string units of the result.

If the second argument is not specified:

• If the graphic-expression is the empty string constant, the length attribute of the result is 0.
• Otherwise, the length attribute of the result is the lower of the following values:
  • The maximum length for the GRAPHIC data type in the string units of the result
  • The length attribute of the first argument

The result is a fixed-length graphic string. The length attribute of the result is determined by the value of integer.

The actual length of the result is the same as the length attribute of the result.

If the length of graphic-expression is less than the length attribute of the result, the result is padded with blanks up to the length attribute of the result.

If the length of graphic-expression is greater than the length attribute of the result, several scenarios exist:

• If the string unit of the result is CODEUNITS32, truncation is performed. If only blank characters are truncated and graphic-expression is GRAPHIC or VARGRAPHIC, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004).
• If integer is specified, truncation is performed. If only blank characters are truncated and graphic-expression is GRAPHIC or VARGRAPHIC, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004). In a Unicode database, when the output string is truncated, such that the last character is a high surrogate, that surrogate is converted to the blank character (X'0020'). Do not rely on this behavior because it might change in a future release.
• If integer is not specified and graphic-expression is VARGRAPHIC, truncation behavior is as follows:
  • If only blank characters must be truncated, truncation is performed with no warning returned.
  • If non-blank characters must be truncated, an error is returned (SQLSTATE 22001).
• If integer is not specified and graphic-expression is DBCLOB, an error is returned (SQLSTATE 22001).

Datetime to graphic

datetime-expression

An expression that is of one of the following data types:

DATE

The result is the graphic string representation of the date in the format specified by the second argument. The length of the result is 10. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703).

TIME

The result is the graphic string representation of the time in the format specified by the second argument. The length of the result is 8. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703).

TIMESTAMP

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

The code page of the string is the code page of the section.

Notes

• Increasing portability of applications: The CAST specification should be used to increase the portability of applications when the first argument is numeric, or if the first argument is a string and the length argument is specified.

Examples

• Example 1: The EDLEVEL column is defined as SMALLINT. The following returns the value as a fixed-length graphic string.

    SELECT GRAPHIC(EDLEVEL) 
      FROM EMPLOYEE 
      WHERE LASTNAME = 'HAAS'

  Results in the value G'18 '.
• Example 2: The SALARY and COMM columns are defined as DECIMAL with a precision of 9 and a scale of 2. Return the total income for employee Haas using the comma decimal character.

    SELECT GRAPHIC(SALARY + COMM, ',') 
      FROM EMPLOYEE 
      WHERE LASTNAME = 'HAAS'

  Results in the value G'56970,00 '.