The CHAR function returns a fixed-length character string representation.
The representations are:
- A character string, if the first argument is any type of character string.
- A datetime value, if the first argument is a date, time, or timestamp.
- 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.
- An integer number, if the first argument is a SMALLINT, INTEGER, or BIGINT.
The first argument must be of a built-in data type. The result of the
CHAR 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. The first argument cannot be an XML value. To convert
an XML value to a CHAR of a specified length, you must use the SQL/XML serialization
operator XMLSERIALIZE.
Character to character syntax
CHAR (CharacterExpression [, integer] )
- CharacterExpression
- An expression that returns a value that is CHAR, VARCHAR, LONG VARCHAR,
or CLOB data type.
- integer
- The length attribute for the resulting fixed length character string.
The value must be between 0 and 254.
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, truncation is performed. A warning
is returned unless the truncated characters were all blanks and the character-expression
was not a long string (LONG VARCHAR or CLOB).
Integer to character syntax
CHAR (IntegerExpression )
- IntegerExpression
- An expression that returns a value that is an integer data type (either
SMALLINT, INTEGER or BIGINT).
The result is the character string representation 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 first argument is a small integer: The length of the result is
6. If the number of characters in the result is less than 6, then the result
is padded on the right with blanks to length 6.
- If the first argument is a large integer: The length of the result is
11. If the number of characters in the result is less than 11, then the result
is padded on the right with blanks to length 11.
- If the first argument is a big integer: The length of the result is 20.
If the number of characters in the result is less than 20, then the result
is padded on the right with blanks to length 20.
Datetime to character syntax
CHAR (DatetimeExpression )
- DatetimeExpression
- An expression that is one of the following three data types:
- date: The result is the character representation of the date. The
length of the result is 10.
- time: The result is the character representation of the time. The
length of the result is 8.
- timestamp: The result is the character string representation of
the timestamp. The length of the result is 26.
Decimal to character
CHAR (DecimalExpression )
- DecimalExpression
- An expression that returns a value that is a decimal data type. If a different
precision and scale is desired, the DECIMAL scalar function can be used first
to make the change.
Floating point to character syntax
CHAR (FloatingPointExpression )
- FloatingPointExpression
- An expression that returns a value that is a floating-point data type
(DOUBLE or REAL).
Example
Use the CHAR function to return the values for EDLEVEL (defined
as smallint) as a fixed length character string:
SELECT CHAR(EDLEVEL) FROM EMPLOYEE
An
EDLEVEL of 18 would be returned as the CHAR(6) value '18????????' (18 followed by
four blanks).