VARGRAPHIC scalar function

Integer to vargraphic:

Decimal to vargraphic:

Floating-point to vargraphic:

Decimal floating-point to vargraphic:

Character to vargraphic:

Graphic to vargraphic:

Datetime to vargraphic:

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

In a Unicode database, if a supplied argument is a character string, it is first converted to a graphic string before the function is executed. 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.

The result of the function is a varying-length graphic string (VARGRAPHIC data type). 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 vargraphic

integer-expression

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

The result is the varying-length graphic string representation of integer-expression in the form of an SQL integer constant. The length attribute of the result depends on whether integer-expression is a small, large or big integer as follows:

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

The actual length of the result is the smallest number of double-byte characters that can be used to represent the value of the argument. Leading zeroes are not included. If the argument is negative, the first double-byte character of the result is a minus sign; otherwise, the first double-byte character is a digit.

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

Decimal to vargraphic

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 varying-length graphic string representation of decimal-expression in the form of an SQL decimal constant. The length attribute of the result is 2+p, where p is the precision of decimal-expression. The actual length of the result is the smallest number of double-byte characters that can be used to represent the result, except that trailing zeros are included. Leading zeros are not 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.

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

Floating-point to vargraphic

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 varying-length graphic string representation of floating-point-expression in the form of an SQL floating-point constant.

The maximum length of the result is 24. The actual length of 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 the decimal-character 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.

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

Decimal floating-point to vargraphic

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 varying-length graphic string representation of decimal-floating-point-expression in the form of an SQL decimal floating-point constant. The maximum length of the result is 42. The actual length of 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.

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

Character to vargraphic

character-expression

An expression that returns a value that is a built-in character string data type (CHAR, VARCHAR, or CLOB).

integer

The length attribute for the resulting varying-length graphic string. The value must be between 0 and 16 336. 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:
  • 16336
  • The length attribute of the first argument
  If the actual length of the first argument (excluding trailing blanks) is greater than 16336, an error is returned (SQLSTATE 22001).

The actual length of the result is the minimum of the length attribute of the result and the actual length of character-expression. If the length of the character-expression is greater than the length attribute of the result, truncation is performed with no warning returned.

Each single-byte character in character-expression is converted to its equivalent double-byte representation or to the double-byte substitution character in the result. Each double-byte character in character-expression is mapped without additional conversion. If the first byte of a double-byte character appears as the last byte of character-expression, it is converted to the double-byte substitution character. The sequential order of the characters in character-expression is preserved.

For a Unicode database, this function converts the character string from the code page of the argument to UCS-2. Every character of the argument, including double-byte characters, is converted. If a value for the second argument is provided, it specifies the required length of the resulting string (in UCS-2 characters).

The conversion to double-byte code points by the VARGRAPHIC function is based on the code page of the argument.

Double-byte characters of the argument are not converted. All other characters are converted to their corresponding double-byte equivalents. If there is no corresponding double-byte equivalent, the double-byte substitution character for the code page is used.

No warning or error code is generated if one or more double-byte substitution characters are returned in the result.

Graphic to vargraphic

graphic-expression

An expression that returns a value that is a built-in graphic string data type (GRAPHIC, VARGRAPHIC, or DBCLOB).

integer

The length attribute for the resulting varying-length graphic string. The value must be between 0 and 16 336. 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:
  • 16336
  • The length attribute of the first argument
  If the actual length of the first argument (excluding trailing blanks) is greater than 16336, an error is returned (SQLSTATE 22001).

The actual length of the result is the minimum of the length attribute of the result and the actual length of graphic-expression. If the length of the graphic-expression is greater than the length attribute of the result, truncation is performed. A warning is returned (SQLSTATE 01004) unless the truncated characters were all blanks and the graphic-expression was not a DBCLOB.

If the length of the graphic expression is greater than the length attribute of the result, the result is truncated. A warning is returned (SQLSTATE 01004), unless the truncated characters were all blanks, and the graphic expression was not a DBCLOB.

Datetime to vargraphic

datetime-expression

An expression that is 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 second argument must not be specified (SQLSTATE 42815).

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

Examples

• The EDLEVEL column is defined as SMALLINT. The following returns the value as a varying-length graphic string.

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

  Results in the value G'18'.
• 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 VARGRAPHIC(SALARY + COMM, ',') 
      FROM EMPLOYEE 
      WHERE LASTNAME = 'HAAS'

  Results in the value G'56970,00'.