​
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):

PatternDescription
9 Digit position (may be dropped if insignificant)
0Digit position (never dropped)
.Decimal point
,Group (thousands) separator
DDecimal point
GGroup separator
SPlus/minus sign directly before or after a number
PLPlus sign in the specified position (for negative numbers)
MIMinus sign in specified position (for positive numbers)
SGPlus/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
SQRTOverview