CHAR scalar function

The CHAR function returns a fixed-length character string representation of a value of a different data type.

Integer to CHAR

Read syntax diagramSkip visual syntax diagramCHAR(integer-expression )

Decimal to CHAR

Read syntax diagramSkip visual syntax diagramCHAR(decimal-expression ,decimal-character)

Floating-point to CHAR

Read syntax diagramSkip visual syntax diagram CHAR(floating-point-expression ,decimal-character )

Decimal floating-point to CHAR

Read syntax diagramSkip visual syntax diagramCHAR(decimal-floating-point-expression ,decimal-character )

Character string to CHAR

Read syntax diagramSkip visual syntax diagramCHAR(character-expression ,integer)

Graphic string to CHAR

Read syntax diagramSkip visual syntax diagramCHAR(graphic-expression ,integer)

Binary string to CHAR

Read syntax diagramSkip visual syntax diagram CHAR ( binary-expression ,integer )

Datetime to CHAR

Read syntax diagramSkip visual syntax diagramCHAR(datetime-expression ,ISOUSAEURJISLOCAL)

Boolean to CHAR

Read syntax diagramSkip visual syntax diagram CHAR ( boolean-expression )

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.

Integer to CHAR
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-aligned. If the data type of the first argument is:
  • SMALLINT, the length of the result is 6
  • INTEGER, the length of the result is 11
  • BIGINT, 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 CHAR
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. 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 CHAR
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 CHAR
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 string to CHAR
character-expression
An expression that returns a value that is a built-in character string data type.
integer
An integer constant that specifies the length attribute for the resulting fixed-length character string. The value must be between 0 and the maximum length for the CHAR data type in the string units of the result.
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:
    • The maximum length for the CHAR data type in the string units of the result
    • The length attribute of the first argument.

The result is a fixed-length character string. If character-expression is FOR BIT DATA, the result is FOR BIT DATA.

The length of the result 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 up to the length attribute of the result
  • Greater than the length attribute of the result:
    • If the string unit of the result is CODEUNITS32, truncation is performed. If only blank characters are truncated and character-expression is CHAR or VARCHAR, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004).
    • If integer is specified, truncation is performed. If only blank characters are truncated and character-expression is CHAR or VARCHAR, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004). When part of a multi-byte character is truncated, that partial character is replaced with the blank character. Do not rely on this behavior because it might change in a future release.
    • If integer is not specified and character-expression is VARCHAR, truncation behavior is:
      • If only blank characters must be truncated, truncation is performed with no warning returned.
      • If non-blank characters must be truncated, an error is returned (SQLSTATE 22001).
    • If integer is not specified and character-expression is CLOB, an error is returned (SQLSTATE 22001).
Graphic string to CHAR
graphic-expression
An expression that returns a value that is a built-in graphic string data type.
integer
An integer constant that specifies the length attribute for the resulting fixed-length character string. The value must be between 0 and the maximum length for the CHAR data type in the string units of the result.
If the second argument is not specified:
  • If the graphic-expression is the empty string constant, the length attribute of the result is 0.
  • If the string units of graphic-expression is CODEUNITS32, the length attribute of the result is the lower of the following values:
    • The maximum length for the CHAR data type in the string units of the result.
    • The length attribute of the first argument.
  • Otherwise, the length attribute of the result is the lower of the following values:
    • The maximum length for the CHAR data type in the string units of the result.
    • 3 * length attribute of the first argument.

The result is a fixed-length character string that is converted from graphic-expression. The length of the result is the same as the length attribute of the result.

If the length of graphic-expression that is converted to a character string is:
  • Less than the length attribute of the result, the result is padded with blanks up to the length attribute of the result.
  • Greater than the length attribute of the result:
    • If the string unit of the result is CODEUNITS32, truncation is performed. If only blank characters are truncated and graphic-expression is GRAPHIC or VARGRAPHIC, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004).
    • If integer is specified and graphic-expression is a GRAPHIC or VARGRAPHIC, truncation is performed with no warning returned.
    • If integer is specified and graphic-expression is a DBCLOB, truncation is performed with a warning returned (SQLSTATE 01004).
    • If integer is not specified, an error is returned (SQLSTATE 22001).
Binary string to CHAR
binary-expression
An expression that returns a value that is a built-in binary string data type.
integer
An integer constant that specifies the length attribute for the resulting fixed-length character string.

The result is a fixed-length FOR BIT DATA character string, padded with blanks if necessary.

Datetime to CHAR
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.

Boolean to CHAR
boolean-expression
An expression that returns a Boolean value (TRUE or FALSE). The result is either 'TRUE ' (note the blank after the E) or 'FALSE'.

Result

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
  • A Boolean value (TRUE or FALSE)
In a non-Unicode database, the string units of the result is OCTETS. Otherwise, the string units of the result are determined by the data type of the first argument.
  • OCTETS, if the first argument is character string or a graphic string with string units of OCTETS, CODEUNITS16, or double bytes.
  • CODEUNITS32, if the first argument is character string or a graphic string with string units of CODEUNITS32.
  • Determined by the default string unit of the environment, if the first argument is not a character string or a graphic string.
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.

If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.

Notes

  • Increasing portability of applications: If the first argument is numeric, or if the first argument is a string and the length argument is specified, use the CAST specification instead of this function to increase the portability of your applications.
  • 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 the dec_to_char_fmt database configuration parameter 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

  • Example 1: 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)
  • Example 2: 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)
  • Example 3: 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)
  • Example 4: 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
  • Example 5: 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
  • Example 6: 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, ',')
  • Example 7: 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)
  • Example 8: 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))
  • Example 9: 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)
  • Example 10: The following statement returns a string of data type CHAR with the value 'TRUE '.
       values CHAR(3=3)
  • Example 11: The following statement returns a string of data type CHAR with the value 'FALSE'.
       values CHAR(3>3)