The
CHAR function returns a fixed-length character string representation
of a variety of input data types.
Integer to character
>>-CHAR--(--integer-expression--)------------------------------><
Decimal to character
>>-CHAR--(--decimal-expression--+----------------------+--)----><
'-,--decimal-character-'
Floating-point to character
>>-CHAR--(--floating-point-expression--+----------------------+--)-><
'-,--decimal-character-'
Decimal floating-point to character
>>-CHAR--(--decimal-floating-point-expression--+----------------------+--)-><
'-,--decimal-character-'
Character to character
>>-CHAR--(--character-expression--+------------+--)------------><
'-,--integer-'
Graphic to character
>>-CHAR--(--graphic-expression--+------------+--)--------------><
'-,--integer-'
Datetime to character
>>-CHAR--(--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 SYSFUN.CHAR(floating-point-expression)
signature continues to be available. In this case, the decimal character
is locale sensitive, and therefore returns either a period or a comma,
depending on the locale of the database server.
The CHAR function returns a fixed-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 fixed-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 character
-
- integer-expression
- An expression that returns a value that is of an integer data
type (SMALLINT, INTEGER, or BIGINT).
The result is
a fixed-length character
string representation of
integer-expression in
the form of an SQL integer constant. The result consists of
n characters,
which represent the significant digits in the argument, and is preceded
by a minus sign if the argument is negative. The result is left-aligned.
- If the first argument is a small integer, the length of the result
is 6.
- If the first argument is a large integer, the length of the result
is 11.
- If the first argument is a big integer, the length of the result
is 20.
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
single-byte blanks.
The
code page of the result is the code page of the section.
- Decimal to character
-
- decimal-expression
- An expression that returns a value that is a decimal data type.
If a different precision and scale are required, the DECIMAL scalar
function can be used first 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 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 fixed-length
character string representation of decimal-expression in
the form of an SQL decimal constant. The length of the result is
2+p, where p is the precision of decimal-expression.
Leading zeros are not included. Trailing zeros are 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. 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 single-byte blanks.
The
code page of the result is the code page of the section.
- Floating-point to character
-
- 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 fixed-length
character string representation of floating-point-expression in
the form of an SQL floating-point constant. The length of the result
is 24. 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 a period 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. If the number of bytes in the result is less
than 24, the result is padded on the right with single-byte blanks.
The
code page of the result is the code page of the section.
- Decimal floating-point to character
-
- 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 fixed-length
character string representation of decimal-floating-point-expression in
the form of an SQL decimal floating-point constant. The length attribute
of the result is 42. 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 warning
when converted to a string. If the number of characters in the result
is less than 42, the result is padded on the right with single-byte blanks.
The
code page of the result is the code page of the section.
- Character to character
-
- 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 fixed-length character string.
The value must be between 0 and 254.
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:
- 254
- The length attribute of the first argument
If the actual length of the first argument (excluding trailing
blanks) is greater than 254, an error is returned (SQLSTATE 22001).
The actual length of the result is the same as
the length attribute of the result. If the length of the character-expression is
less than the length of the result, the result is padded with blanks
up to the length of the result. 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.
If the length of the character expression is less
than the length attribute of the result, the result is padded with
blanks up to the length of the result. If the length of the character
expression is greater than the length attribute of the result, the
result is truncated. A warning is returned (SQLSTATE 01004), unless
the truncated characters were all blanks, and the character expression
was not a CLOB.
- Graphic to character
- 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 fixed-length character
string. The value must be between 0 and 254.
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:
- 254
- 3 * length attribute of the first argument
If the actual length of the first argument (including trailing
blanks) is greater than 254, an error is returned (SQLSTATE 22001).
The actual length of the result is the same as
the length attribute of the result. If the length of the graphic-expression is
less than the length of the result, the result is padded with blanks
up to the length of the result. If the length of the graphic-expression is
greater than the length attribute of the result, truncation is performed with
no warning returned.
- Datetime to character
-
- 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 is not applicable and 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 fixed-length string is a FOR BIT DATA character
string, padded with blanks if necessary.
- Decimal to character and leading zeros: In versions previous
to version 9.7, the result for decimal input to this function includes
leading zeros and a trailing decimal character. The database configuration
parameter dec_to_char_fmt can be set to "V95" to have
this function return the version 9.5 result for decimal input. The
default value of dec_to_char_fmt for new databases is "NEW",
which has this function return results which match the SQL standard
casting rules and is consistent with results from the VARCHAR function.
Examples
- Example 1: Assume that the PRSTDATE column has an internal
value equivalent to 1988-12-25. The following function returns the
value ‘12/25/1988'.
CHAR(PRSTDATE, USA)
- Example 2: Assume that the STARTING column has an internal
value equivalent to 17:12:30, and that the host variable HOUR_DUR
(decimal(6,0)) is a time duration with a value of 050000 (that is,
5 hours). The following function returns the value ‘5:12 PM'.
CHAR(STARTING, USA)
The following
function returns the value ‘10:12 PM'. CHAR(STARTING + :HOUR_DUR, USA)
- Example 3: Assume that the RECEIVED column (TIMESTAMP)
has an internal value equivalent to the combination of the PRSTDATE
and STARTING columns. The following function returns the value ‘1988-12-25-17.12.30.000000'.
CHAR(RECEIVED)
- Example 4: The LASTNAME column is defined as VARCHAR(15).
The following function returns the values in this column as fixed-length
character strings that are 10 bytes long. LASTNAME values that are
more than 10 bytes long (excluding trailing blanks) are truncated
and a warning is returned.
SELECT CHAR(LASTNAME,10) FROM EMPLOYEE
- Example 5: The EDLEVEL column is defined as SMALLINT. The
following function returns the values in this column as fixed-length
character strings. An EDLEVEL value of 18 is returned as the CHAR(6)
value ‘18' followed by four blanks.
SELECT CHAR(EDLEVEL) FROM EMPLOYEE
- Example 6: The SALARY column is defined as DECIMAL with
a precision of 9 and a scale of 2. The current value (18357.50) is
to be displayed with a comma as the decimal character (18357,50).
The following function returns the value ‘18357,50'
followed by three blanks.
CHAR(SALARY, ',')
- Example 7: Values in the SALARY column are to be subtracted
from 20000.25 and displayed with the default decimal character. The
following function returns the value ‘-0001642.75' followed by three blanks.
CHAR(20000.25 - SALARY)
- Example 8: Assume that the host variable SEASONS_TICKETS
is defined as INTEGER and has a value of 10000. The following function
returns the value ‘10000.00'.
CHAR(DECIMAL(:SEASONS_TICKETS,7,2))
- Example 9: Assume that the host variable DOUBLE_NUM is
defined as DOUBLE and has a value of -987.654321E-35. The following
function returns the value ‘-9.87654321E-33'
followed by nine trailing blanks because the result data type is CHAR(24).
CHAR(:DOUBLE_NUM)