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 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 (CHAR, VARCHAR, or CLOB).
- integer
- The
length attribute for the resulting varying-length character string.
The value must be between 0 and 32 672.
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:
- 32672
- The length attribute of the first argument
If the actual length of the first argument (excluding trailing
blanks) is greater than 32672, 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.
A warning is returned (SQLSTATE 01004) unless the truncated characters
were all blanks and the
character-expression was
not a CLOB.
- Graphic to varchar
-
- 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 character
string. The value must be between 0 and 32 672.
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:
- 32672
- 3 * length attribute of the first argument
If the actual length of the first argument (excluding trailing
blanks) is greater than 32672, 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-expressionis
greater than the length attribute of the result, truncation is performed
with no warning returned.
- 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.
For more information, see "CAST specification".
- 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'.