VARGRAPHIC scalar function

The VARGRAPHIC function returns a varying-length graphic string representation of the first argument.

Start of change

Syntax for integer to vargraphic

FL 502

Read syntax diagramSkip visual syntax diagramVARGRAPHIC( integer-expression)
End of change
Start of change

Syntax for decimal to vargraphic

FL 502

Read syntax diagramSkip visual syntax diagramVARGRAPHIC( decimal-expression,decimal-character)
End of change
Start of change

Syntax for floating-point to vargraphic

FL 502

Read syntax diagramSkip visual syntax diagramVARGRAPHIC( floating-point-expression)
End of change
Start of change

Syntax for decimal floating-point to vargraphic

FL 502

Read syntax diagramSkip visual syntax diagramVARGRAPHIC( decimal-floating-point-expression)
End of change

Syntax for character to vargraphic:

Read syntax diagramSkip visual syntax diagramVARGRAPHIC( character-expression,integer,CODEUNITS16CODEUNITS32)

Syntax for graphic to vargraphic:

Read syntax diagramSkip visual syntax diagramVARGRAPHIC( graphic-expression,integer,CODEUNITS16CODEUNITS32)

The schema is SYSIBM.

The GRAPHIC function returns a varying length-graphic-string representation of:

  • Start of changeFL 502 An integer number if the first argument is a SMALLINT, INTEGER, or BIGINTEnd of change
  • Start of changeFL 502 A decimal number if the first argument is a decimal numberEnd of change
  • Start of changeFL 502 A double-precision floating-point number if the first argument is a DOUBLE or REALEnd of change
  • Start of changeFL 502 A decimal floating-point number if the first argument is DECFLOATEnd of change
  • A character string if the first argument is any type of character string
  • A graphic string if the first argument is an EBCDIC or Unicode graphic string

The result can be null; if the first argument is null, the result is the null value.

The length attribute and actual length of the result are measured in double-byte characters because the result is a graphic string.

Start of change

Integer to vargraphic

FL 502

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

The result is a varying-length graphic string of the argument in the form of an SQL integer constant. The actual length of the result is the smallest number of characters that can be used to represent the value of the argument. 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. A positive value starts with a digit. Leading zeroes are not included.

  • 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 CCSID of the result is 1200 (UTF-16).

End of change
Start of change

Decimal to vargraphic

FL 502

decimal-expression
An expression that returns a value that is a built-in decimal data type. If a different precision and scale is wanted, the DECIMAL scalar function can be used to make the change. To specify a different precision and scale for the value of the expression, apply the DECIMAL function before applying the VARGRAPHIC function.
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result graphic string. The character must not be a digit, a plus sign (+), a minus sign (-), or a blank. The default is the period (.) or comma (,). For information on what factors govern the choice, see Decimal point representation.

The result is a varying-length graphic string representation of the first argument. The actual length of the result is the smallest number of characters that can be used to represent the result, except that trailing zeros are included. 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. A positive value starts with a digit or the decimal-character, and always includes at least one trailing blank. Leading zeros are not returned. If the scale of decimal-expression is zero, the decimal character is not returned. If the number of bytes in the result is less than the defined length of the result, the result is padded on the right with blanks.

The length of the result is 2+p where p is the precision of the decimal-expression.

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

End of change
Start of change

Floating-point to vargraphic

FL 502

floating-point-expression
An expression that returns a value that is a floating-point data type (DOUBLE or REAL).

The result is a varying-length graphic string representation of the argument in the form of an SQL floating-point constant. If the argument is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If the argument is zero, the result is 0E0.

The length of the result is 24. The actual length of the result includes 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 a period and a sequence of digits. If the number of characters in the result is less than 24, the result is padded on the right with blanks.

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

End of change
Start of change

Decimal floating-point to vargraphic

FL 502

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

The result is a varying-length graphic string representation of the argument in the form of an SQL decimal floating-point constant.

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

The length 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 the period. 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 a letter if the DECFLOAT value is Infinity, sNaN, or NaN.

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

End of change

Character to vargraphic

character-expression
An expression that returns a value of a built-in character string data type that contains an EBCDIC-encoded or Unicode-encoded character string value. It cannot be BIT data. The argument does not need to be mixed data, but any occurrences of X'0E' and X'0F' in the string must conform to the rules for EBCDIC mixed data. (See Character strings for these rules.)
integer
The length attribute of the resulting varying-length graphic string. The value must be an integer constant in the range 1–16352.

If CODEUNITS16 or CODEUNITS32 is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.

If integer is not specified and if the character-expression is an empty string constant or has a value X'0E0F', the length attribute of the result is 1 and the result is an empty string. Otherwise, the length attribute of the result is the same as the length attribute of the first argument.

CODEUNITS16 or CODEUNITS32
Specifies the unit that is used to express integer. If CODEUNITS16 or CODEUNITS32 is specified, the input is EBCDIC, and there is no corresponding CCSID for EBCDIC GRAPHIC data, an error occurs.
CODEUNITS16
Specifies that integer is expressed in terms of 16-bit UTF-16 code units.
CODEUNITS32
Specifies that integer is expressed in terms of 32-bit UTF-32 code units.

For more information about CODEUNITS16 and CODEUNITS32, see String unit specifications.

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 character-expression, as measured in single-byte characters, is greater than the specified length of the result, as measured in double-byte characters, the result is truncated. Unless all the truncated characters are blanks appropriate for character-expression, a warning is returned.

The CCSID of the result is the graphic CCSID that corresponds to the character CCSID of character-expression. If the input is EBCDIC and there is no system CCSID for EBCDIC GRAPHIC data, the CCSID of the result is X'FFFE'.

For EBCDIC input data:

Each character of character-expression determines a character of the result. The argument might need to be converted to the native form of mixed data before the result is derived. Let M denote the system CCSID for mixed data. The argument is not converted if any of the following conditions is true:

  • The argument is mixed data and its CCSID is M.
  • The argument is SBCS data and its CCSID is the same as the system CCSID for SBCS data. In this case, the operation proceeds as if the CCSID of the argument is M.

Otherwise, the argument is a new string S derived by converting the characters to the coded character set identified by M. If there is no system CCSID for mixed data, conversion is to the coded character set that the system CCSID for SBCS data identifies.

The result is derived from S using the following steps:

  • Each shift character (X'0E' or X'0F') is removed.
  • Each double-byte character remains as is.
  • Each single-byte character is replaced by a double-byte character.

The replacement for a single-byte character is the equivalent DBCS character if an equivalent exists. Otherwise, the replacement is X'FEFE'. The existence of an equivalent character depends on M. If there is no system CCSID for mixed data, the DBCS equivalent of X'xx' for EBCDIC is X'42xx', except for X'40', whose DBCS equivalent is X'4040'.

For Unicode input data:

Each character of character-expression determines a character of the result. The argument might need to be converted to the native form of mixed data before the result is derived. Let M denote the system CCSID for mixed data. The argument is not converted if any of the following conditions is true:

  • The argument is mixed data, and its CCSID is M.
  • The argument is SBCS data, and its CCSID is the same as the system CCSID for SBCS data. In this case, the operation proceeds as if the CCSID of the argument is M.

Otherwise, the argument is a new string S derived by converting the characters to the coded character set identified by M.

The result is derived from S using the following steps:

  • Each non-supplementary character is replaced by a Unicode double-byte character (a UTF-16 code point). A non-supplementary character in UTF-8 is in the range 1–3 bytes.
  • Each supplementary character is replaced by a pair of Unicode double-byte characters (a pair of UTF-16 code points).

The replacement for a single-byte character is the Unicode equivalent character if an equivalent exists. Otherwise, the replacement is X'FFFD'.

Graphic to vargraphic

graphic-expression
An expression that returns a value of a built-in graphic string data type that contains an EBCDIC-encoded or Unicode-encoded graphic string value.
integer
The length attribute for the resulting varying-length graphic string. The value must be an integer constant in the range 1–16352.

If CODEUNITS16 or CODEUNITS32 is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.

If integer is not specified and if the graphic-expression is an empty string constant, the length attribute of the result is 1 and the result is an empty string. Otherwise, the length attribute of the result is the same as the length attribute of the first argument.

CODEUNITS16 or CODEUNITS32
Specifies the unit that is used to express integer. If CODEUNITS16 or CODEUNITS32 is specified, the input is EBCDIC, and there is no corresponding CCSID for EBCDIC GRAPHIC data, an error occurs.
CODEUNITS16
Specifies that integer is expressed in terms of 16-bit UTF-16 code units.
CODEUNITS32
Specifies that integer is expressed in terms of 32-bit UTF-32 code units.

For more information about CODEUNITS16 and CODEUNITS32, see String unit specifications.

The actual length of the result depends on the number of characters in graphic-expression. If the length of graphic-expression is greater than the length specified, the result is truncated. Unless all of the truncated characters are double-byte blanks, a warning is returned.

The CCSID of the result is the same as the CCSID of graphic-expression.

Start of change

Notes

FL 502 Casting numeric data to Unicode: The result of the VARGRAPHIC functions is Unicode (UTF-16) when the first argument is numeric data. The VARGRAPHIC function can only be invoked with numeric data for the first argument if the containing statement:
  • references Unicode base tables or views only and the statement is qualified to be a single encoding scheme statement, or
  • is considered a multiple encoding scheme statement and the application encoding scheme is Unicode.
End of change

Examples

Start of change
Example 1:

FL 502 Assume that an EMPLOYEE_U table exists that is similar to the EMPLOYEE sample table except that it is a Unicode table. The EDLEVEL column is defined as SMALLINT. Assume that employee Christine Haas was in school for 18 years. The following statement returns the EDLEVEL column value as a varying-length graphic string.

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

The result value is G'18'.

Start of changeExample 2:End of change
Start of change

FL 502 Assume that an EMPLOYEE_U table exists that is similar to the EMPLOYEE sample table except that it is a Unicode table. The SALARY and COMM columns are defined as DECIMAL with a precision of 9 and a scale of 2. Assume that employee Christine Haas has a salary of $52750.00 and a commission of $4220.00. Return the total of the salary and commission for employee Christine Haas as a varying-length graphic string using the comma decimal character.

SELECT VARGRAPHIC(SALARY + COMM, ',')
 FROM EMPLOYEE_U
 WHERE LASTNAME = 'HAAS'

The result value is G'56970,00'.

End of change
End of change