TO_CHAR Function (number)

Description

The TO_CHAR function converts a Number Format or numeric data type to a character string according to the number format and returns it. The type of the return value is VARCHAR. If the number format has not been specified as an argument, all significant figures are converted to a character string according to the default format.

Syntax

TO_CHAR(number_argument[, format_argument ])

 

number_argument :

numeric(decimal)

• integer

• smallint

• bigint

• float(real)

• double

NULL

 

format_argument :

• character strings (see Number Format)

NULL

Number Format

Format Element

Example

Description

9

9999

The number of 9's represents the number of significant figures to be returned.
If the number of significant figures specified in the format is not sufficient, only the decimal part is rounded. If it is less than the number of digits in an integer, # is outputted.
If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with space characters and the decimal part is filled with 0.

0

0999

If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with 0, not space characers before the value is returned.

S

S9999

Outputs the negative/positive sign in the specified position. These signs can be used only at the beginning of character string.

C

C9999

Returns the ISO currency code at the specified position.

,(comma)

9,999

Returns a comma (",") at the specified position. Multiple commas are allowed in the format.

.(percimal point)

9.999

Outputs the decimal point (".") that distinguishes the integer and the decimal part at a specified position. Only one decimal point is allowed in the format.

EEEE

9.99EEEE

Returns a scientific notation number.

Example

--selecting a string casted from a number in the specified format

SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999');

 

============================================

  ' +12345'             '+012345'

 

 

SELECT TO_CHAR(1234567,'C9,999,999,999');

======================

  '    $1,234,567'

 

SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999.99999'), TO_CHAR(123.4567,'99999.999');

 to_char(123.4567, '99', 'en_US')   to_char(123.4567, '999.99999', 'en_US')   to_char(123.4567, '99999.999', 'en_US')

==========================================================

  '##'                  '123.45670'           '  123.457'

 

 

SELECT TO_CHAR(1.234567,'99.999EEEE'), TO_CHAR(1.234567E-4);

 to_char(1.234567, '99.999EEEE', 'en_US')   to_char(1.234567E-4)

============================================

  '1.235E+00'           '0.0001234567'