The
VARCHAR function returns a varying-length character string representation
of a variety of data types.
Integer to varchar
>>-VARCHAR--(--integer-expression--)---------------------------><
Decimal to varchar
>>-VARCHAR--(--decimal-expression--+----------------------+--)-><
'-,--decimal-character-'
Floating-point to varchar
>>-VARCHAR--(--floating-point-expression--+----------------------+--)-><
'-,--decimal-character-'
Decimal floating-point to varchar
>>-VARCHAR--(--decimal-floating-point-expression--+----------------------+--)-><
'-,--decimal-character-'
Character to varchar
>>-VARCHAR--(--character-expression--+------------+--)---------><
'-,--integer-'
Graphic to varchar
>>-VARCHAR--(--graphic-expression--+------------+--)-----------><
'-,--integer-'
Datetime to varchar
>>-VARCHAR--(--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 VARCHAR function returns a varying-length character
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
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 (Unicode databases only), if the first argument
is any type of graphic string
- A datetime value, if the first argument is a DATE, TIME, or TIMESTAMP
In a non-Unicode database, the
string units of the result is OCTETS. Otherwise, the string units
of the result is determined by the data type of the first argument.
- OCTETS, if the first argument is character string or a graphic
string with string units of OCTETS, CODEUNITS16, or double bytes.
- 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.
In a Unicode database, when the output string is truncated
part-way through a multiple-byte character:
- If the input was a character string, the partial character is
replaced with one or more blanks
- If the input was a graphic string, the partial character is replaced
by the empty string
Do not rely on either of these behaviors, because they might
change in a future release.
The result of the function is a varying-length character
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 varchar
- 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 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 characters that can be used to represent the value of the argument.
Leading zeros 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.
The
code page of the result is the code page of the section.
- Decimal to varchar
- 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 single-byte character constant that is used to delimit
the decimal digits in the result character string. The 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 character 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 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 character of the result is a minus sign; otherwise,
the first 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 code page of the section.
- Floating-point to varchar
- 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 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 character 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 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 character of the result is a minus sign; otherwise,
the first character is a digit. If floating-point-expression is
zero, the result is 0E0.
The
code page of the result is the code page of the section.
- Decimal floating-point to varchar
- decimal-floating-point-expression
- An expression that returns a value that is a decimal floating-point
data type (DECFLOAT).
- decimal-character
- Specifies the single-byte character constant that is used to delimit
the decimal digits in the result character string. The 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 character 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 characters that can represent the value of decimal-floating-point-expression.
If decimal-floating-point-expression is
negative, the first character of the result is a minus sign; otherwise,
the first 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 "INFINITY", "SNAN",
and "NAN", respectively, are returned. If the special value is
negative, the first 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 code page of the section.
- Character to varchar
-
- character-expression
- An
expression that returns a value that is a built-in character string
data type.
- integer
- An
integer constant that specifies the length attribute for the resulting
varying-length character string. The value must be between 0 and the maximum length for the VARCHAR 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 VARCHAR data type in the string units
of the result
- The length attribute of the first argument
The result is a varying-length character string.
The length attribute of the result is determined by the value of integer.
If character-expression is the FOR BIT DATA
subtype, the result is FOR BIT DATA.
If the length of
character-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 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 part of a multi-byte character is truncated,
that partial character is replaced with the blank character. 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).
- Graphic to varchar
- 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 character string. The value must be between
0 and the maximum length for the VARCHAR
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.
- If the string units of graphic-expression is
CODEUNITS32, the length attribute of the result is the lower of the
following values:
- The maximum length for the VARCHAR data type in the string units
of the result
- The length attribute of the first argument
- Otherwise, the length
attribute of the result is the lower of the following values:
- The maximum length for the VARCHAR data
type in the string units of the result
- 3 * length attribute of the first argument
The result is a varying-length character string
that is converted from graphic-expression.
The length attribute of the result is determined by the value of integer.
If
the length of
graphic-expression that is
converted to a character 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 graphic-expression is
GRAPHIC or VARGRAPHIC, no warning is returned. Otherwise, a warning
is returned (SQLSTATE 01004).
- If integer is specified and graphic-expression is
a GRAPHIC or VARGRAPHIC, truncation is performed with no warning returned.
- If integer is specified and graphic-expression is
a DBCLOB, truncation is performed with a warning returned (SQLSTATE
01004).
- If integer is not specified and graphic-expression is
a GRAPHIC or VARGRAPHIC, truncation is performed with no warning returned.
- If integer is not specified and graphic-expression is
a DBCLOB, an error is returned (SQLSTATE 22001).
- Datetime to varchar
-
- datetime-expression
- An expression that is of one of the following
data types:
- DATE
- The result is the character 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 character 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 character 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 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 the first argument is a string and the length argument is specified.
- A binary string is allowed as the first argument
to the function, and the resulting varying-length string is a FOR
BIT DATA character string.
Examples
- Example 1: Make EMPNO varying-length with a length of 10.
SELECT VARCHAR(EMPNO,10)
INTO :VARHV
FROM EMPLOYEE
- Example 2: Set the host variable JOB_DESC, defined as VARCHAR(8),
to the VARCHAR equivalent of the job description (which is the value
of the JOB column), defined as CHAR(8), for employee Dolores Quintana.
SELECT VARCHAR(JOB)
INTO :JOB_DESC
FROM EMPLOYEE
WHERE LASTNAME = 'QUINTANA'
- Example 3: The EDLEVEL column is defined as SMALLINT. The
following returns the value as a varying-length character string.
SELECT VARCHAR(EDLEVEL)
FROM EMPLOYEE
WHERE LASTNAME = 'HAAS'
Results in the value
'18'.
- Example 4: 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 VARCHAR(SALARY + COMM, ',')
FROM EMPLOYEE
WHERE LASTNAME = 'HAAS'
Results in the value
'56970,00'.