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.
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
Format Element |
Example |
Description |
---|---|---|
9 |
9999 |
The number of 9's represents the number of significant figures to be returned. |
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. |
--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'