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 non-Unicode database, the
string units of the result is OCTETS. Otherwise, the string units
of the result are 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 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.
- integer
- An
integer constant that specifies the length attribute for the resulting
fixed-length character string. The value
must be between 0 and the maximum length for the CHAR 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 CHAR data
type in the string units of the result
- The length attribute of the first argument.
The result is a fixed-length character string.
The length attribute of the result is determined by the value of integer.
If character-expression is FOR BIT DATA,
the result is FOR BIT DATA.
The actual length of the result
is the same as the length attribute of the result. If the length of
character-expression is
less than the length attribute of the result, the result is padded
with blanks up to the length attribute of the result. 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 and character-expression is
VARCHAR, truncation behavior is as follows:
- If only blank characters must be truncated, truncation is performed
with no warning returned.
- If non-blank characters must be truncated, an error is returned
(SQLSTATE 22001).
- If integer is not specified and character-expression is
CLOB, an error is returned (SQLSTATE 22001).
- Graphic to character
- 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 fixed-length character string. The
value must be between 0 and the maximum length for the CHAR 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 CHAR 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 CHAR data
type in the string units of the result.
- 3 * length attribute of the first argument.
The result is a fixed-length character string
that is converted from graphic-expression.
The length attribute of the result is determined by the value of integer.
The
actual length of the result is the same as the length attribute of
the result.
If the length of graphic-expression that
is converted to a character string is less than the length attribute
of the result, the result is padded with blanks up to the length attribute
of the result.
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, an error
is returned (SQLSTATE 22001).
- 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 the dec_to_char_fmt database configuration parameter 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)