TO_CHAR from Number

Overview

The TO_CHAR function formats a number into a string using a given format.

Syntax

The syntax for using the TO_CHAR function is as follows:

TO_CHAR(value, format_string)

Parameters in the syntax include:

• value: A number that will be formatted to a string.
• format: The format of the input string.

Format

Format string supports following template patterns (can be lowercase):

Pattern	Description
9	Digit position (may be dropped if insignificant)
0	Digit position (never dropped)
.	Decimal point
,	Group (thousands) separator
D	Decimal point
G	Group separator
S	Plus/minus sign directly before or after a number
PL	Plus sign in the specified position (for negative numbers)
MI	Minus sign in specified position (for positive numbers)
SG	Plus/minus sign in the specified position.

Limitations

• All text inside double quote "{text}" will not be considered a pattern.
• The quote character "" will not appear in the result string.
• Any text that does not match any pattern will be preserved in the result string.

Examples

Case 1: Formatting with Leading Zeros

The query formats 123.456 with leading zeros using the pattern ‘00000.00000’.

SELECT TO_CHAR(123.456, '00000.00000');

The output displays the formatted number as shown below.

   to_char    
--------------
  00123.45600

Case 2: Formatting with Variable Length

The query formats the number 123.456 with a variable-length pattern ‘99999.99999’.

SELECT TO_CHAR(123.456, '99999.99999');

The output displays the formatted number as shown below.

   to_char    
--------------
    123.45600

Case 3: Formatting with Group

The query formats the number 123456 with grouping separators using the pattern ‘9,999,999,999’.

SELECT TO_CHAR(123456, '9,999,999,999');

It will return the output below.

    to_char     
----------------
        123,456

Case 4: Formatting with Negative Number

The query formats the number -123 with a custom pattern including the sign.

SELECT TO_CHAR(-123, '"Number formatted with pattern:000S":{000S}');

The output shows the custom-formatted number.

                  to_char                  
-------------------------------------------
 Number formatted with pattern:000S:{123-}

Case 5: Formatting with Sign

The query formats the number -123.456 with a custom pattern including the sign and separated integer.

SELECT TO_CHAR(-123.456, '"Sing is: "SG" integer part is: "999", mantissa part is: ".999');

The output shows the customized format as shown below.

                         to_char                         
---------------------------------------------------------
 Sing is: - integer part is: 123, mantissa part is: .456