CHAR
The CHAR function returns a fixed-length character-string representation.
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 if the first argument is any type of 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.
- A row ID value if the first argument is a ROWID.
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 a built-in integer data type (either SMALLINT, INTEGER, or BIGINT).
The result is the fixed-length 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. The result is left justified.
- If the 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.
- If the 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.
- If the 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.
The CCSID of the result is the default SBCS CCSID at the current server.
Decimal to Character
- decimal-expression
- An expression that returns a value that is a built-in 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 fixed-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. If the scale of decimal-expression is zero, the decimal character is not returned.
The length of the result is 2+p where p is the precision of the decimal-expression. This means that a positive value will always include one trailing blank.
The CCSID of the result is the default SBCS CCSID at the current server.
Floating-point to Character
- floating-point expression
- An expression that returns a value that is a built-in 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 fixed-length character-string representation of the argument in the form of a floating-point constant. The length of the result is 24. 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. Otherwise, the result includes the smallest number of characters that can be used to represent the value of the argument such that the mantissa consists of a single digit other than zero followed by a decimal-character and a sequence of digits.
If the number of characters in the result is less than 24, then the result is padded on the right with blanks.
The CCSID of the result is the default SBCS CCSID at the current server.
Decimal floating-point to Character
- decimal-floating-point expression
- An expression that returns a value that is a built-in 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 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 fixed-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.
If the number of characters in the result is less than 42, then the result is padded on the right with blanks.
The CCSID of the result is the default SBCS CCSID at the current server.
Character to Character
- character-expression
- An expression that returns a value that is a built-in character-string data type.1
- length
- An integer constant that specifies the length attribute
for the resulting fixed length character string. The value must be
between 1 and 32766 (32765 if nullable). If the first argument is
DBCS-only
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 the 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 is the same as the length attribute of the result. 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 (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 and the length attribute of the result cannot be less
than 4. 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 and the length
attribute of the result is greater than or equal to 4, then the
result is mixed data. The CCSID of the result is the same as the CCSID
of the first argument.
- If the first argument is mixed data that is DBCS-open
or DBCS-either and the length attribute of the result is less than
4, the CCSID of the result is the associated SBCS CCSID for the mixed
data CCSID.
Graphic to Character
- graphic-expression
- An expression that returns a value that is a built-in graphic-string data type. It must not be DBCS-graphic data.
- length
- An integer constant that specifies the length attribute
for the resulting fixed length character string. The value must be
between 1 and 32766 (32765 if nullable).
If the second argument is not specified
or DEFAULT is specified: - If the graphic-expression is the empty 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 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. 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 and
the length attribute of the result cannot be less than 4. 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
and the length
attribute of the result is greater than or equal to 4, then the
result is mixed data. Otherwise, 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 of the result is 10. Otherwise the 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 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. If datetime-expression is a TIMESTAMP(0), the length of the result is 19. If the data type of datetime-expression is a TIMESTAMP(n), the length of the result is 20+n. Otherwise the length of the result is 26.
- 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.
Examples
- Assume the column PRSTDATE has an internal value equivalent to
1988-12-25. The date format is *MDY and the date separator is a slash
(/).
Results in the value ‘12/25/1988'.SELECT CHAR(PRSTDATE, USA) FROM PROJECT
Results in the value ‘12/25/88'.SELECT CHAR(PRSTDATE) FROM PROJECT - Assume the column STARTING has an internal value equivalent to
17.12.30, the host variable HOUR_DUR (DECIMAL(6,0)) is a time duration
with a value of 050000 (that is, 5 hours).
Results in the value ‘5:12 PM'.SELECT CHAR(STARTING, USA) FROM CL_SCHED
Results in the value ‘10:12:00'.SELECT CHAR(STARTING + :HOUR_DUR, JIS) FROM CL_SCHED - Assume the column RECEIVED (timestamp) has an internal value equivalent
to the combination of the PRSTDATE and STARTING columns.
Results in the value ‘1988-12-25-17.12.30.000000'.SELECT CHAR(RECEIVED) FROM IN_TRAY - Use the CHAR function to make the type fixed-length character
and reduce the length of the displayed results to 10 characters for
the LASTNAME column (defined as VARCHAR(15)) of the EMPLOYEE table.
For rows having a LASTNAME with a length greater than 10 characters (excluding trailing blanks), a warning (SQLSTATE 01004) that the value is truncated is returned.SELECT CHAR(LASTNAME,10) FROM EMPLOYEE - Use the CHAR function to return the values for EDLEVEL (defined
as SMALLINT) as a fixed length string.
An EDLEVEL of 18 would be returned as the CHAR(6) value '18 ' (18 followed by 4 blanks).SELECT CHAR(EDLEVEL) FROM EMPLOYEE - Assume that the same SALARY subtracted from 20000.25 is to be
returned with a comma as the decimal character.
A SALARY of 21150 returns the value '–1149,75 ' (–1149,75 followed by 3 blanks).SELECT CHAR(20000.25 - SALARY, ',') FROM EMPLOYEE - Assume a host variable, DOUBLE_NUM, has a double precision floating-point
data type and a value of -987.654321E-35.
Results in the character value '-9.8765432100000002E-33 '.SELECT CHAR(:DOUBLE_NUM) FROM SYSIBM.SYSDUMMY1