CHAR scalar function

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:

Decimal to Character:

Floating-Point to Character:

Decimal floating-point to Character:

Character to Character:

Graphic to Character:

Datetime to Character:

Row ID to Character:

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 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 value if the first argument is any type of character string
A graphic string if the first argument is an EBCDIC or Unicode graphic 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 is the smallest number of characters that can be used to represent the value of the argument, padded with blanks. 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. A positive value starts with a digit and always includes at least one trailing blank. Leading zeroes are not included. 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, 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, 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, the result is padded on the right with blanks.

A positive value always includes one trailing blank.

The CCSID of the result is determined from the application encoding scheme.

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 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 first argument. The result is the smallest number of characters that can be used to represent the value of the argument, except that trailing zeros are included.

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. A positive value starts with a digit or the decimalcharacter, and always includes at least one trailing blank. Leading zeros are not 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.¹

The length of the result is 2 +p, where p is the precision of the decimal-expression.

The CCSID of the result is determined from the application encoding scheme.

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. If the argument is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If the argument is zero, the result is 0E0.

The length of the result is 24. 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.

The CCSID of the result is determined from the application encoding scheme.

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.

If the result value is Infinity, sNaN, or NaN, the strings 'INFINITY', 'SNAN', and 'NAN', respectively, are returned. The DECFLOAT special value sNaN does not result in an exception when converted to a string.

The length of the result is 42. If the number of characters in the result is less than 42, the result is padded on the right with blanks. 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 a letter if the result value is Infinity, sNaN, or NaN.

The CCSID of the result is determined from the application encoding scheme.

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 in the range 1–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.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.

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 in the range 1–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.

For more information about CODEUNITS16 and CODEUNITS32, see String unit specifications.

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.

Recommendation: To increase the portability of applications, use the CAST specification 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.

Notes

Syntax alternatives:: CHAR9 can be specified as an alternative to CHAR, except when the first argument is decimal data. See CHAR9.

Examples

Example 1:

HIREDATE is a DATE column in sample table DSN8D10.EMP. When it represents the date 15 December 1976 (as it does for employee 140), the following example returns the string value '12/15/1976' in character string variable DATESTRING:

   EXEC SQL SELECT CHAR(HIREDATE, USA)
     INTO :DATESTRING
     FROM DSN8D10.EMP
     WHERE EMPNO = '000140';

Example 2:

Host variable HOUR has a data type of DECIMAL(6,0) and contains a value of 50000. Interpreted as a time duration, this value is 5 hours. Assume that STARTING is a TIME column in some table. Then, when STARTING represents 17 hours, 30 minutes, and 12 seconds after midnight, the following example returns the value '10:30 PM':

   CHAR(STARTING+:HOURS, USA)

Example 3:

Assume that RECEIVED is defined as a TIMESTAMP column in table TABLEY. When the value of the date portion of RECEIVED represents the date 10 March 1997 and the time portion represents 6 hours and 15 seconds after midnight, the following example returns the string value '1997-03-10-06.00.15.000000':

   SELECT CHAR(RECEIVED)
     FROM TABLEY
     WHERE INTCOL = 1234;

Example 4:

For sample table DSN8D10.EMP, the following SQL statement sets the host variable AVERAGE, which is defined as CHAR(33), to the character string representation of the average employee salary.

   EXEC SQL SELECT CHAR(AVG(SALARY))
     INTO :AVERAGE
     FROM DSN8D10.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.

Example 5:

For the rows in sample table DSN8D10.EMP, return the values in column LASTNAME, which is defined as VARCHAR(15), as a fixed-length character string and limit the length of the results to 10 characters.

   SELECT CHAR(LASTNAME,10)
     FROM DSN8D10.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.

Example 6:

FIRSTNAME is a VARCHAR(12) column in a Unicode table T1. One of its values is the 6-character string 'Jürgen'. When FIRSTNAME has the values shown under 'Function', the results are shown under 'Returns':

 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'

Example 7

For the rows in sample table DSN8D10.EMP, return the values in column EDLEVEL, which is defined as SMALLINT, as a fixed-length character string.

   SELECT CHAR(EDLEVEL)
     FROM DSN8D10.EMP;

An EDLEVEL of 18 is returned as CHAR(6) value '18 ' (18 followed by four blanks).

Example 8:

In sample table DSN8D10.EMP, the SALARY column is defined as DECIMAL(9,2). For those employees who have a salary of 52750.00, return the hire date and the salary, using a comma as the decimal character in the salary (52750,00).

   SELECT HIREDATE, CHAR(SALARY, ',')
     FROM DSN8D10.EMP
     WHERE SALARY = 52750.00;

The salary is returned as the string value '52750,00'.

Example 9:

Repeat the scenario in Example 8 except subtract the SALARY column from 60000.00 and return the salary with the default decimal character.

   SELECT HIREDATE, CHAR (60000.00 - SALARY)
     FROM DSN8D10.EMP
     WHERE SALARY = 52750.00;

The salary is returned as the string value '7250.00'.

Example 10:

Assume that host variable SEASONS_TICKETS is defined as INTEGER and has a value of 10000. Use the DECIMAL and CHAR functions to change the value into the character string ' 10000.00'.

   SELECT CHAR(DECIMAL(:SEASONS_TICKETS,7,2))
     FROM SYSIBM.SYSDUMMY1;

Example 11:

Assume that columns COL1 and COL2 in table T1 are both defined as REAL and that T1 contains a single row with the values 7.1E+1 and 7.2E+2 for the two columns. Add the two columns and represent the result as a character string.

   SELECT CHAR(COL1 + COL2)
     FROM T1;

The result is the character value '1.43E2 '.

¹ If the function is invoked as CHAR and the BIF_COMPATIBILITY subsystem parameter is set to V9_DECIMAL_VARCHAR, or if the function is invoked as SYSCOMPAT_V9.CHAR, the result is formatted the same as the result of the CHAR9 function.