The
VARGRAPHIC function returns a varying-length graphic string representation
of a variety of data types.
Integer to vargraphic:
>>-VARGRAPHIC--(--integer-expression--)------------------------><
Decimal to vargraphic:
>>-VARGRAPHIC--(--decimal-expression--+----------------------+--)-><
'-,--decimal-character-'
Floating-point to vargraphic:
>>-VARGRAPHIC--(--floating-point-expression--+-------------------------+-><
'-,--decimal-character--)-'
Decimal floating-point to vargraphic:
>>-VARGRAPHIC--(--decimal-floating-point-expression--+-------------------------+-><
'-,--decimal-character--)-'
Character to vargraphic:
>>-VARGRAPHIC--(--character-expression--+---------------+------><
'-,--integer--)-'
Graphic to vargraphic:
>>-VARGRAPHIC--(--graphic-expression--+------------+--)--------><
'-,--integer-'
Datetime to vargraphic:
>>-VARGRAPHIC--(--datetime-expression--+--------------+--)-----><
'-,--+-ISO---+-'
+-USA---+
+-EUR---+
+-JIS---+
'-LOCAL-'
The schema is SYSIBM. The function
name cannot be specified as a qualified name when keywords are used
in the function signature.
The VARGRAPHIC function returns a varying-length graphic
string representation of:
- An integer number (Unicode database only), if
the first argument is a SMALLINT, INTEGER, or BIGINT
- A decimal number (Unicode database only), if
the first argument is a decimal number
- A double-precision floating-point number (Unicode
database only), if the first argument is a DOUBLE or REAL
- A decimal floating-point number (Unicode database
only), if the first argument is a decimal floating-point number (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 datetime value (Unicode databases only), if the first argument is a DATE, TIME, or TIMESTAMP
In a non-Unicode database, the
string units of the result is double bytes. Otherwise, the string
units of the result is determined by the data type of the first argument.
- CODEUNITS16, if the first argument is character string or a graphic
string with string units of OCTETS or CODEUNITS16.
- CODEUNITS32, if the first argument is character string or a graphic
string with string units of CODEUNITS32.
- Determined by the default string unit of the environment, if the
first argument is not a character string or a graphic string.
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 zeros 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
- 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 varying-length graphic string. The value must be between
0 and the maximum length for the VARGRAPHIC
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 VARGRAPHIC data type in the string
units of the result.
- The length attribute of the first argument.
The result is a varying-length graphic string
that is converted from character-expression.
The length attribute of the result is determined by the value of integer.
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 with a warning returned (SQLSTATE 01004). When the output
string is truncated, such that the last character is a high surrogate,
that surrogate is deleted. Do not rely on this behavior because it
might change in a future release.
- If integer is not specified and character-expression is
a VARCHAR, truncation is performed with a warning returned (SQLSTATE
01004).
- If integer is not specified and character-expression
is a CLOB, an error is returned (SQLSTATE 22001).
This function converts character-expression from
UTF-8 to UTF-16. Every character of character-expression is
converted.
- 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 varying-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 VARGRAPHIC data type.
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 databases
with a code set that is not Japanese EUC (code page 954) or Traditional
Chinese (code page 964), 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 extra 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. No warning or error code is generated if one or more double-byte
substitution characters are returned in the result.
For details
about the conversion process for databases with a code set that is
Japanese EUC (code page 954) or Traditional Chinese (code page 964),
refer to the Related Links section.
- Graphic to vargraphic
- graphic-expression
- An
expression that returns a value that is a built-in graphic string
data type.
- integer
- An
integer constant that specifies the length attribute for the resulting
varying-length graphic string. The value must be between 0 and the maximum length for the VARGRAPHIC 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 VARGRAPHIC
data type in the string units of the result
- The length attribute of the first argument
The result is a varying-length graphic string. The length
attribute of the result is determined by the value of integer.
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, an error
is returned (SQLSTATE 22001).
- 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.
Notes
- 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. For more
information, see "CAST specification".
Examples
- Example 1: 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'.
- 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 VARGRAPHIC(SALARY + COMM, ',')
FROM EMPLOYEE
WHERE LASTNAME = 'HAAS'
Results in the value
G'56970,00'.