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.

Start of change 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.¹ End of change

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.

Start of change 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. End of change

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.

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 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.

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. End of change

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. End of change

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.

Example 1: HIREDATE is a DATE column in sample table DSN8A10.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 DSN8A10.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 DSN8A10.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 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.

Example 5: For the rows in sample table DSN8A10.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 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.

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 DSN8A10.EMP, return the values in column EDLEVEL, which is defined as SMALLINT, as a fixed-length character string.

   SELECT CHAR(EDLEVEL)
     FROM DSN8A10.EMP;

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

Example 8: In sample table DSN8A10.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 DSN8A10.EMP
     WHERE SALARY = 52750.00;

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

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 DSN8A10.EMP
     WHERE SALARY = 52750.00;

The salary is returned as the string value '7250.00'. End of change

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 function. End of change