The VARCHAR function returns a character-string representation.
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------------------------------>
>--+--------------------------------+--)-----------------------><
'-,--+-length--+--+------------+-'
'-DEFAULT-' '-,--integer-'
Graphic to Varchar

>>-VARCHAR (--graphic-expression-------------------------------->
>--+--------------------------------+--)-----------------------><
'-,--+-length--+--+------------+-'
'-DEFAULT-' '-,--integer-'
Datetime to Varchar

>>-VARCHAR--(--datetime-expression--+--------------+--)--------><
'-,--+-ISO---+-'
+-USA---+
+-EUR---+
+-JIS---+
'-LOCAL-'
The VARCHAR function returns a 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 packed or zoned 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 DECFLOAT.
- A character string if the first argument is any type of character
string.
- A graphic string if the first argument is any graphic string.
- A date value if the first argument is a DATE.
- A time value if the first argument is a TIME.
- A timestamp value if the first argument is a TIMESTAMP.
The result of the function is a varying-length 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 an integer data type
(either SMALLINT, INTEGER, or BIGINT).
The result is a varying-length character string
of the argument in the form of an SQL integer constant. 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. It is left justified.
- 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 actual length of the result is the smallest number
of characters that can be used to represent the value of the argument.
Leading zeroes 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 or the decimal-character.
The CCSID of the result is the default SBCS CCSID at the
current server.
Decimal to Varchar
- decimal-expression
- An expression that returns a value that is a packed or zoned decimal
data type (either DECIMAL or NUMERIC). If a different precision and
scale is wanted, the DECIMAL scalar function can be used to make the
change.
- decimal-character
- Specifies the single-byte character constant that is used to delimit
the decimal digits in the result character string. The character must
be a period or comma. If the second argument is not specified, the
decimal point is the default decimal point. For more information,
see Decimal point.
The result is a varying-length character string representation
of the argument. 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. Leading zeros
are not returned. Trailing zeros are returned.
The length attribute of the result is 2+p where p is
the precision of the 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 characters
are included. Leading zeros are not included. If the argument is negative,
the result begins with a minus sign. Otherwise, the result begins
with a digit or the decimal-character.
The CCSID of the result is the default SBCS CCSID at
the current server.
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 must
be a period or comma. If the second argument is not specified, the
decimal point is the default decimal point. For more information,
see Decimal point.
The result is a varying-length character string representation
of the argument in the form of a floating-point constant.
The length attribute of the result is 24. The actual length
of the result is 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 the decimal-character and
a sequence of digits. If the argument 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 argument
is zero, the result is 0E0.
The CCSID of the result is the default
SBCS CCSID at the current server.
Decimal floating-point to Varchar
- decimal-floating-point expression
- An expression that returns a value that is a decimal floating-point
data type.
- decimal-character
- Specifies the single-byte character constant that is used to delimit
the decimal digits in the result character string. The character must
be a period or comma. If the second argument is not specified, the
decimal point is the default decimal point. For more information,
see Decimal point.
The result is a varying-length character string
representation of the argument in the form of a decimal floating-point
constant.
The length attribute 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 decimal-character.
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 the decimal-character. If
the argument is zero, the result is 0.
If the DECFLOAT value
is Infinity, sNaN, or NaN, the strings
'INFINITY', 'SNAN', and 'NAN', respectively, are returned. If the
special value is negative, a minus sign will be the first character
in the string. The DECFLOAT special value sNaN does not result
in an exception when converted to a string.
The CCSID of the
result is the default SBCS CCSID at the current server.
Character to Varchar
- character-expression
- An expression that returns a value that is a built-in CHAR, VARCHAR,
or CLOB data type.1
- length
- An integer constant that specifies the length attribute
for the resulting varying length character string. The value must
be between 1 and 32740 (32739 if nullable). If the first argument
is mixed data, the second argument cannot be less than 4.
If the
second argument is not specified or DEFAULT is specified:
- If the character-expression is an empty string constant,
the length attribute of the result is 1.
- Otherwise, the length attribute of the result is the same as the
length attribute of the first argument.
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 (SQLSTATE 01004) is returned unless the truncated characters
were all blanks.
- integer
- An integer constant that specifies the CCSID of the result.
It must be a valid SBCS CCSID, mixed data CCSID, or 65535 (bit data).
If the third argument is an SBCS CCSID, then the result is SBCS data.
If the third argument is a mixed CCSID, then the result is mixed data.
If the third argument is 65535, then the result is bit data. If the
third argument is a SBCS CCSID, then the first argument cannot be
a DBCS-either or DBCS-only string.
If the third argument is not
specified then:
- If the first argument is SBCS data, then the result is SBCS data.
The CCSID of the result is the same as the CCSID of the first argument.
- If the first argument is mixed data (DBCS-open, DBCS-only, or
DBCS-either), then the result is mixed data. The CCSID of the result
is the same as the CCSID of the first argument.
Graphic to Varchar
- graphic-expression
- An expression that returns a value that is a GRAPHIC, VARGRAPHIC,
and DBCLOB data type. It must not be DBCS-graphic data.
- length
- An integer constant that specifies the length attribute
for the resulting varying length character string. The value must
be between 1 and 32740 (32739 if nullable). If the first argument
contains DBCS data, the second argument cannot be less than 4.
If
the second argument is not specified or DEFAULT is specified, the
length attribute of the result is determined as follows (where n is
the length attribute of the first argument):
- If the graphic-expression is the empty graphic string constant,
the length attribute of the result is 1.
- If the result is SBCS data, the result length is n.
- If the result is mixed data, the result length is (2.5*(n-1))
+ 4.
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 character-expression is
greater than the length attribute of the result, truncation is performed.
A warning (SQLSTATE 01004) is returned unless the truncated characters
were all blanks.
- integer
- An integer constant that specifies the CCSID of the result.
It must be a valid SBCS CCSID or mixed data CCSID. If the third argument
is an SBCS CCSID, then the result is SBCS data. If the third argument
is a mixed CCSID, then the result is mixed data. The third argument
cannot be 65535.
If the third argument is not specified, the CCSID
of the result is the default CCSID at the current server. If the default
CCSID is mixed data, then the result is mixed data. If the default
CCSID is SBCS data, then the result is SBCS data.
Datetime to Character
- datetime-expression
- An expression that is one of the following three built-in data
types
- date
- The
result is the character-string representation of the date in the format
specified by the second argument. If the second argument is not specified,
the format used is the default date format. If the format is ISO,
USA, EUR, or JIS, the length attribute and actual length of the result
is 10. Otherwise the length attribute and actual length of the result
is the length of the default date format. For more information see String representations of datetime values.
- time
- The result is the character-string representation of the time
in the format specified by the second argument. If the second argument
is not specified, the format used is the default time format. The
length attribute and actual length of the result is 8. For more information
see String representations of datetime values.
- timestamp
- The second argument is not applicable and must not be specified.
The
result is the character-string representation of the timestamp. The
length attribute and actual length of the result is 26.
The CCSID of the string is the default SBCS CCSID at
the current server.
- ISO, EUR, USA, or JIS
- Specifies the date or time format of the resulting character string.
For more information, see String representations of datetime values.
- LOCAL
- Specifies that the date or time format of the resulting character
string should come from the DATFMT, DATSEP, TIMFMT, and TIMSEP attributes
of the job at the current server.
Note
Syntax alternatives: The CAST
specification should be used to increase the portability of applications
when the first argument is a string and the length argument is specified.
For more information, see CAST specification.