DB2 Version 9.7 for Linux, UNIX, and Windows

CHAR scalar function

Integer to character


>>-CHAR--(--integer-expression--)------------------------------><

Decimal to character


>>-CHAR--(--decimal-expression--+----------------------+--)----><
                                '-,--decimal-character-'

Floating-point to character


>>-CHAR--(--floating-point-expression--+----------------------+--)-><
                                       '-,--decimal-character-'

Decimal floating-point to character


>>-CHAR--(--decimal-floating-point-expression--+----------------------+--)-><
                                               '-,--decimal-character-'

Character to character


>>-CHAR--(--character-expression--+------------+--)------------><
                                  '-,--integer-'

Graphic to character


>>-CHAR--(--graphic-expression--+------------+--)--------------><
                                '-,--integer-'

Datetime to character


>>-CHAR--(--datetime-expression--+--------------+--)-----------><
                                 '-,--+-ISO---+-'      
                                      +-USA---+        
                                      +-EUR---+        
                                      +-JIS---+        
                                      '-LOCAL-'

The schema is SYSIBM. The function name cannot be specified as a qualified name when keywords are used in the function signature. The SYSFUN.CHAR(floating-point-expression) signature continues to be available. In this case, the decimal character is locale sensitive, and therefore returns either a period or a comma, depending on the locale of the database server.

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 (Unicode databases only), if the first argument is any type of graphic string
A datetime value, if the first argument is a DATE, TIME, or TIMESTAMP

In a Unicode database, when the output string is truncated part-way through a multiple-byte character:

If the input was a character string, the partial character is replaced with one or more blanks
If the input was a graphic string, the partial character is replaced by the empty string

Do not rely on either of these behaviors, because they might change in a future release.

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 of an integer data type (SMALLINT, INTEGER, or BIGINT).

The result is a fixed-length character string representation of integer-expression in the form of an SQL integer constant. The result consists of n characters, which represent the significant digits in the argument, and is preceded by a minus sign if the argument is negative. The result is left justified.

If the first argument is a small integer, the length of the result is 6.
If the first argument is a large integer, the length of the result is 11.
If the first argument is a big integer, the length of the result is 20.

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 single-byte blanks.

The code page of the result is the code page of the section.

Decimal to character

decimal-expression: An expression that returns a value that is a decimal data type. If a different precision and scale are required, the DECIMAL scalar function can be used first 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 constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a fixed-length character string representation of decimal-expression in the form of an SQL decimal constant. The length of the result is 2+p, where p is the precision of decimal-expression. Leading zeros are not included, however if you are working with a migrated database from an earlier version of the DB2 software, the returned output will include leading zeroes and a trailing decimal character. Trailing zeros are included. If decimal-expression 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 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 single-byte blanks.

The code page of the result is the code page of the section.

Floating-point to character

floating-point-expression: An expression that returns a value that is a 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 constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a fixed-length character string representation of floating-point-expression in the form of an SQL floating-point constant. The length of the result is 24. The result is the smallest number of characters that can represent the value of floating-point-expression such that the mantissa consists of a single digit other than zero followed by a period and a sequence of digits. If floating-point-expression is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If floating-point-expression is zero, the result is 0E0. If the number of bytes in the result is less than 24, the result is padded on the right with single-byte blanks.

The code page of the result is the code page of the section.

Decimal floating-point to character

decimal-floating-point-expression: An expression that returns a value that is a 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 constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a fixed-length character string representation of decimal-floating-point-expression in the form of an SQL decimal floating-point constant. The length attribute of the result is 42. The result is the smallest number of characters that can represent the value of decimal-floating-point-expression. If decimal-floating-point-expression is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If decimal-floating-point-expression is zero, the result is 0.

If the value of decimal-floating-point-expression is the special value Infinity, sNaN, or NaN, the strings 'INFINITY', 'SNAN', and 'NAN', respectively, are returned. If the special value is negative, the first character of the result is a minus sign. The decimal floating-point special value sNaN does not result in warning when converted to a string. If the number of characters in the result is less than 42, the result is padded on the right with single-byte blanks.

The code page of the result is the code page of the section.

Character to character

character-expression

An expression that returns a value that is a built-in character string data type (CHAR, VARCHAR, or CLOB).

integer

The length attribute for the resulting fixed-length character string. The value must be between 0 and 254.

If the second argument is not specified:

If the character-expression is the empty string constant, the length attribute of the result is 0.
Otherwise, the length attribute of the result is the lower of the following values:
- 254
- The length attribute of the first argument
If the actual length of the first argument (excluding trailing blanks) is greater than 254, an error is returned (SQLSTATE 22001).

The actual length of the result is the same as the length attribute of the result. If the length of the character-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 character-expression is greater than the length attribute of the result, truncation is performed. A warning is returned (SQLSTATE 01004) unless the truncated characters were all blanks and the character-expression was not a CLOB.

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, the result is truncated. A warning is returned (SQLSTATE 01004), unless the truncated characters were all blanks, and the character expression was not a CLOB.

Graphic to character

graphic-expression

An expression that returns a value that is a built-in graphic string data type. (GRAPHIC, VARGRAPHIC, or DBCLOB).

integer

The length attribute for the resulting fixed-length character string. The value must be between 0 and 254.

If the second argument is not specified:

If the graphic-expression is the empty string constant, the length attribute of the result is 0.
Otherwise, the length attribute of the result is the lower of the following values:
- 254
- 3 * length attribute of the first argument
If the actual length of the first argument (including trailing blanks) is greater than 254, an error is returned (SQLSTATE 22001).

The actual length of the result 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 with no warning returned.

Datetime to character

datetime-expression

An expression that is of one of the following data types:

DATE: The result is the character string representation of the date in the format specified by the second argument. The length of the result is 10. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703).
TIME: The result is the character string representation of the time in the format specified by the second argument. The length of the result is 8. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703).
TIMESTAMP: The result is the character string representation of the timestamp. If the data type of datetime-expression is TIMESTAMP(0), the length of the result is 19. If the data type of datetime-expression is TIMESTAMP(n), where n is between 1 and 12, the length of the result is 20+n. Otherwise, the length of the result is 26. The second argument is not applicable and must not be specified (SQLSTATE 42815).

The code page of the result is the code page of the section.

Notes:

The CAST specification should be used to increase the portability of applications 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".
A binary string is allowed as the first argument to the function, and the resulting fixed-length string is a FOR BIT DATA character string, padded with blanks if necessary.
Decimal to character and leading zeros: In versions previous to version 9.7, the result for decimal input to this function includes leading zeros and a trailing decimal character. The database configuration parameter dec_to_char_fmt can be set to "V95" to have this function return the version 9.5 result for decimal input. The default value of dec_to_char_fmt for new databases is "NEW", which has this function return results which match the SQL standard casting rules and is consistent with results from the VARCHAR function.

Examples

Assume that the PRSTDATE column has an internal value equivalent to 1988-12-25. The following function returns the value '12/25/1988'.
```
   CHAR(PRSTDATE, USA)
```
Assume that the STARTING column has an internal value equivalent to 17:12:30, and that the host variable HOUR_DUR (decimal(6,0)) is a time duration with a value of 050000 (that is, 5 hours). The following function returns the value '5:12 PM'.
```
   CHAR(STARTING, USA)
```
The following function returns the value '10:12 PM'.
```
   CHAR(STARTING + :HOUR_DUR, USA)
```
Assume that the RECEIVED column (TIMESTAMP) has an internal value equivalent to the combination of the PRSTDATE and STARTING columns. The following function returns the value '1988-12-25-17.12.30.000000'.
```
   CHAR(RECEIVED)
```
The LASTNAME column is defined as VARCHAR(15). The following function returns the values in this column as fixed-length character strings that are 10 bytes long. LASTNAME values that are more than 10 bytes long (excluding trailing blanks) are truncated and a warning is returned.
```
   SELECT CHAR(LASTNAME,10) FROM EMPLOYEE
```
The EDLEVEL column is defined as SMALLINT. The following function returns the values in this column as fixed-length character strings. An EDLEVEL value of 18 is returned as the CHAR(6) value '18' followed by four blanks.
```
   SELECT CHAR(EDLEVEL) FROM EMPLOYEE
```
The SALARY column is defined as DECIMAL with a precision of 9 and a scale of 2. The current value (18357.50) is to be displayed with a comma as the decimal character (18357,50). The following function returns the value '18357,50' followed by three blanks.
```
   CHAR(SALARY, ',')
```
Values in the SALARY column are to be subtracted from 20000.25 and displayed with the default decimal character. The following function returns the value '-0001642.75' followed by three blanks.
```
   CHAR(20000.25 - SALARY)
```
Assume that the host variable SEASONS_TICKETS is defined as INTEGER and has a value of 10000. The following function returns the value '10000.00'.
```
   CHAR(DECIMAL(:SEASONS_TICKETS,7,2))
```
Assume that the host variable DOUBLE_NUM is defined as DOUBLE and has a value of -987.654321E-35. The following function returns the value '-9.87654321E-33' followed by nine trailing blanks because the result data type is CHAR(24).
```
   CHAR(:DOUBLE_NUM)
```