CHAR
The CHAR function returns a fixed-length character string representation of the argument.
The syntax of the CHAR function depends on the data type of the input argument. The following types of input arguments are accepted.
Integer to Character:
>>-CHAR(integer-expression)------------------------------------><
Decimal to Character:
>>-CHAR(decimal-expression-+----------------------+-)---------->< '-,--decimal-character-'
Floating-Point to Character:
>>-CHAR(floating-point-expression)-----------------------------><
Decimal floating-point to Character:
>>-CHAR(decimal-floating-point-expression)---------------------><
Character to Character:
>>-CHAR(character-expression-+------------------------------------+-)->< '-,--integer--+--------------------+-' '-,--+-CODEUNITS16-+-' +-CODEUNITS32-+ '-OCTETS------'
Graphic to Character:
>>-CHAR(graphic-expression-+------------------------------------+-)->< '-,--integer--+--------------------+-' '-,--+-CODEUNITS16-+-' '-CODEUNITS32-'
Datetime to Character:
>>-CHAR(datetime-expression-+--------------+-)----------------->< '-,--+-ISO---+-' +-USA---+ +-EUR---+ +-JIS---+ '-LOCAL-'
Row ID to Character:
>>-CHAR(row-ID-expression)-------------------------------------><
The schema is SYSIBM.
The CHAR function returns a fixed-length character string representation of one of the following values:
- An integer number if the first argument is a small, large, or big integer
- A decimal number if the first argument is a decimal number
- A floating-point number if the first argument is a single or double precision floating-point number
- A decimal floating-point number if the first argument is a decimal floating-point number
- A character string value if the first argument is any type of string
- A datetime value if the first argument is a date, time, or timestamp
- A row ID value if the first argument is a row ID
The result of the function is a fixed-length character string (CHAR).
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 (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. If the argument is negative, the result has a preceding minus sign. The result is left justified, and its length depends on whether the argument is a small or large integer:
- For a small integer, the length of the result is 6. If the number of characters in the result is less than 6, the result is padded on the right with blanks to a length of 6.
- For a large integer, the length of the result is 11; if the number of characters in the result is less than 11, the result is padded on the right with blanks to a length of 11.
A positive value always includes one trailing blank.
The CCSID of the result is determined from the context in which the function is invoked. For more information, see Determining the encoding scheme and CCSID of a string.
- Decimal to Character
- decimal-expression
- An expression that returns a value that is a built-in decimal data type. To specify a different precision and scale for the value of the expression, apply the DECIMAL function before applying the CHAR function.
- decimal-character
- Specifies the single-byte character constant (CHAR or VARCHAR) that is used to delimit the decimal digits in the result character string. The character must not be a digit, a plus sign (+), a minus sign (-), or a blank. The default is the period (.) or comma (,). For information on what factors govern the choice, see Decimal point representation.
The result is the 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 the 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. 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 blanks.1
The leading blank is not returned for CAST(decimal-expression AS CHAR(n)).
The length of the result is2 +p, where p is the precision of the decimal-expression.
The CCSID of the result is determined from the context in which the function was invoked. For more information, see Determining the encoding scheme and CCSID of a string.
- 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).
The result is the fixed-length character string representation of the argument in the form of an SQL floating-point constant. The length of the result is 24 bytes.
If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit. If the value of the argument is zero, the result is 0E0. Otherwise, the result includes 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 a period and a sequence of digits.
If the number of characters in the result is less than 24, the result is padded on the right with blanks to length of 24.
The CCSID of the result is determined from the context in which the function is invoked. For more information, see Determining the encoding scheme and CCSID of a string.
- 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).
The result is the fixed-length character string representation of the argument in the form of an SQL decimal floating-point constant. The length of the result is 42 bytes. If the number of characters in the result is less than 42, the result is padded on the right with blanks to length of 42.
If the DECFLOAT value is one of the special values Infinity, sNaN, or NaN, the strings ’INFINITY’, ’SNAN’, or ’NAN’, respectively, are returned. If the special value is negative, a minus sign is the first character in the returned string. The DECFLOAT special value sNaN does not result in an exception when it is converted to a string.
The CCSID of the result is determined from the context in which the function is invoked. For more information, see Determining the encoding scheme and CCSID of a string.
- Character to Character
- character-expression
- An expression that returns a value of a built-in character string.
- integer
- The length attribute for the resulting fixed-length character
string. The value must be an integer constant between 1 and 255.
If the length is not specified, the length attribute of the result is the minimum of 255 and the length attribute of character-expression. If character-expression is an empty string constant, an error occurs.
If CODEUNITS16 or CODEUNITS32 is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.
- CODEUNITS16, CODEUNITS32, or OCTETS
- Specifies the unit that is used to express integer.
If character-expression is
a character string that is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot
be specified.
- CODEUNITS16
- Specifies that integer is expressed in terms of 16-bit UTF-16 code units.
- CODEUNITS32
- Specifies that integer is expressed in terms of 32-bit UTF-32 code units.
- OCTETS
- Specifies that integer is expressed in terms of bytes.
The actual length 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 to the length of the result. If the length of character-expression is greater than the length attribute of the result, the result is truncated. Unless all of the truncated characters are blanks, a warning is returned.
If character-expression is bit data, the result is bit data. Otherwise, the CCSID of the result is the same as the CCSID of character-expression.
- Graphic to Character
- graphic-expression
- An expression that returns a value of a built-in graphic string.
- integer
- The length attribute for the resulting fixed-length character
string. The value must be an integer constant between 1 and 255.
If the length is not specified, the length attribute of the result is the minimum of 255 and the length attribute of graphic-expression. The length attribute of graphic-expression is (3 * length(graphic-expression)). If graphic-expression is an empty string constant, an error occurs.
If CODEUNITS16 or CODEUNITS32 is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.
- CODEUNITS16 or CODEUNITS32
- Specifies the unit that is used to express integer.
- CODEUNITS16
- Specifies that integer is expressed in terms of 16-bit UTF-16 code units.
- CODEUNITS32
- Specifies that integer is expressed in terms of 32-bit UTF-32 code units.
The actual length is the same as the length attribute of the result. If the length of graphic-expression is less than the length attribute of the result, the result is padded with blanks to the length of the result. If the length of graphic-expression is greater than the length attribute of the result, the result is truncated. Unless all of the truncated characters are blanks, a warning is returned.
The CCSID of the result is the character mixed CCSID that corresponds to the graphic CCSID of graphic-expression.
- Datetime to Character
- datetime-expression
- An expression that is one of the following built-in data types:
- date
- The result is the character string representation of the date
in the format that is specified by the second argument. If the second
argument is omitted, the DATE precompiler option, if one is provided,
otherwise field DATE FORMAT on installation panel DSNTIP4 specifies
the format. If the format is LOCAL, field
LOCAL DATE LENGTH on installation panel DSNTIP4 specifies the length
of the result. Otherwise, the length of the result is 10.
LOCAL denotes the local format at the DB2® subsystem that executes the SQL statement. If LOCAL is used for the format, a date exit routine must be installed at that DB2 subsystem.
An error occurs if the second argument is specified and is not a valid value.
- time
- The result is the character string representation of the time in the format that is specified by
the second argument. If the second argument is omitted, the TIME precompiler option, if one is
provided, otherwise field TIME FORMAT on installation panel DSNTIP4 specifies the format. If the
format is LOCAL, the field LOCAL TIME LENGTH on installation panel DSNTIP4
specifies the length of the result. Otherwise, the length of the result is 8.
LOCAL denotes the local format at the DB2 subsystem that executes the SQL statement. If LOCAL is used for the format, a time exit routine must be installed at that DB2 subsystem.
An error occurs if the second argument is specified and is not a valid value.
- timestamp without time zone
- The result is the character string representation of the timestamp. If datetime-expression is a TIMESTAMP(0) value, the length of the result is 19. If datetime-expression is a TIMESTAMP(integer) value, the length of the result is 20+integer. Otherwise, the length of the result is 26. The second argument must not be specified.
- timestamp with time zone
- The result is the character string representation of the timestamp with time zone, formatted as yyyy-mm-dd-hh.mm.ss.nnnnnn±th:tm with the appropriate number of 'n' characters for the precision of the timestamp. If datetime-expression is a TIMESTAMP(0) WITH TIME ZONE, the length of the result is 147. If datetime-expression is a TIMESTAMP(integer) WITH TIME ZONE, the length of the result is 148+integer. The second argument must not be specified.
The CCSID of the result is determined from the context in which the function is invoked. For more information, see Determining the encoding scheme and CCSID of a string.
- ISO, EUR, USA, JIS, or LOCAL
- Specifies the date or time format of the resulting character string. For more information, see String representations of datetime values.
- Row ID to Character
- row-ID-expression
- An expression that returns a value that is a built-in row ID data type.
The result is the fixed-length character string representation of the argument. The result is bit data.
The length of the result is 40. If the length of row-ID-expression is less than 40, the result is padded on the right with hexadecimal zeros to a length of 40.
EXEC SQL SELECT CHAR(HIREDATE, USA)
INTO :DATESTRING
FROM DSN8A10.EMP
WHERE EMPNO = '000140';
CHAR(STARTING+:HOURS, USA)
SELECT CHAR(RECEIVED)
FROM TABLEY
WHERE INTCOL = 1234;
EXEC SQL SELECT CHAR(AVG(SALARY))
INTO :AVERAGE
FROM DSN8A10.EMP;
With DEC31, the result of AVG applied to a decimal number is a decimal number with a precision of 31 digits. The only host languages in which such a large decimal variable can be defined are Assembler and C. For host languages that do not support such large decimal numbers, use the method shown in this example.
SELECT CHAR(LASTNAME,10)
FROM DSN8A10.EMP;
For
rows that have a LASTNAME with a length greater than 10 characters
(excluding trailing blanks), a warning that the value is truncated
is returned. Function ... Returns ...
-----------------------------------------------------------------------------------
CHAR(FIRSTNAME,3,CODEUNITS32) 'Jür ' -- x'4AC3BC722020202020202020'
CHAR(FIRSTNAME,3,CODEUNITS16) 'Jür ' -- x'4AC3BC722020202020'
CHAR(FIRSTNAME,3,OCTETS) 'Jü' -- x'4AC3BC'
SELECT CHAR(EDLEVEL)
FROM DSN8A10.EMP;
An
EDLEVEL of 18 is returned as CHAR(6) value '18 ' (18 followed by
four blanks). SELECT HIREDATE, CHAR(SALARY, ',')
FROM DSN8A10.EMP
WHERE SALARY = 52750.00;
The salary is returned as the string
value '52750,00'. SELECT HIREDATE, CHAR (60000.00 - SALARY)
FROM DSN8A10.EMP
WHERE SALARY = 52750.00;
The salary is returned as the string
value '7250.00'. SELECT CHAR(DECIMAL(:SEASONS_TICKETS,7,2))
FROM SYSIBM.SYSDUMMY1;
SELECT CHAR(COL1 + COL2)
FROM T1;
The result is the character value '1.43E2 '.