When you use the TO_CHAR() function to format a number in Oracle, you use a format model to determine how the number should be formatted.

For example, you could format a number like 12,345.00 or like 12.345,00, depending on your locale.

The format model can include the G or D format elements to add a comma to a number. Which one you use depends on whether you want the comma as a thousands separator, or as a decimal character.

Alternatively, you can use an actual comma character (,) if you prefer, although this method is not locale aware like the G and D format elements are.

The G and D Format Elements

Here’s an example to demonstrate the G and D format elements:

SELECT TO_CHAR(12345, 'fm99G999D00')
FROM DUAL;

Result:

12,345.00

In this case, the group separator outputs a comma, and the decimal character outputs a period. That’s because my current session’s NLS_TERRITORY parameter is set to Australia.

Here’s what happens if I change my NLS_TERRITORY parameter to Germany:

ALTER SESSION SET NLS_TERRITORY = 'Germany';
SELECT TO_CHAR(12345, 'fm99G999D00')
FROM DUAL;

Result:

12.345,00

Now the comma is being used for the decimal character.

As a quick explainer of the above format model:

• The fm format modifier suppresses any padding that might be applied to the result.
• The 9 characters represent numbers.
• The 0 character represents numbers without suppressing any leading or trailing zeros.

Here’s a full list of number format elements that you can use as a quick reference.

The NLS_NUMERIC_CHARACTERS Parameter

When we set the NLS_TERRITORY parameter (like in the previous example), this implicitly sets a bunch of other parameters, including the NLS_NUMERIC_CHARACTERS parameter.

The NLS_NUMERIC_CHARACTERS parameter determines which characters are used for the group separator and decimal character.

We can query the V$NLS_PARAMETERS view to see which characters are being used for the group separator and decimal character:

SELECT VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';

Result:

,.

Here we see that the decimal character is represented by a comma, and the group separator is represented by a period.

You can change the value of the NLS_NUMERIC_CHARACTERS parameter directly if you want (i.e. without changing the NLS_TERRITORY parameter).

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
SELECT TO_CHAR(12345, 'fm99G999D00')
FROM DUAL;

Result:

12,345.00

But you should probably avoid doing this, because it causes a disconnect between NLS parameters. Your NLS parameters no longer reflect the default values for the current territory. Unless you have good reason not to, it’s usually better to change the NLS_TERRITORY parameter to the relevant territory, so that other parameters can also be updated to their default for the new territory.

The 'nlsparam' Argument

One thing I should mention is that the T0_CHAR() function accepts a third argument that enables you to temporarily set various NLS parameters, including the NLS_NUMERIC_CHARACTERS parameter. When you do this at the function level, it doesn’t change the value of those parameters for the current session.

Here’s an example:

ALTER SESSION SET NLS_TERRITORY = 'Germany';
SELECT 
    TO_CHAR(12345, 'fm99G999D00') AS "r1",
    TO_CHAR(
        12345, 'fm99G999D00',
        'NLS_NUMERIC_CHARACTERS = ''.,'''
        ) AS "r2",
    TO_CHAR(12345, 'fm99G999D00') AS "r3"
FROM DUAL;

Result:

          r1           r2           r3 
____________ ____________ ____________ 
12.345,00    12,345.00    12.345,00   

Here, I set the session’s territory to Germany, and then called TO_CHAR() three times.

• The first call uses the session’s NLS parameters. This means that the default group separator is a period.
• In the second call, I explicitly set my own NLS_NUMERIC_CHARACTERS parameter from within the function. In this case, I set the group separator to be a comma. Doing this did not affect my session’s NLS parameters, as seen by the third call.
• The third call uses the session’s NLS parameters, just like the first call. As we can see, the group separator and decimal character haven’t been affected by the (temporary) change that we did in our second call.

Hardcoded Comma

Another way to add a comma to a number is to hardcode it into your format model.

Example:

ALTER SESSION SET NLS_TERRITORY = 'Germany';
SELECT TO_CHAR(12345, 'fm99,999.00')
FROM DUAL;

Result:

12,345.00

In this case I hardcoded the comma and the period. Doing this ignores the group separator set in the NLS_NUMERIC_CHARACTERS parameter.

Multiple Commas

You can have multiple commas and/or group separators within a format model.

Example:

ALTER SESSION SET NLS_TERRITORY = 'Australia';
SELECT TO_CHAR(123456789, 'fm999G999G999D00')
FROM DUAL;

Result:

123,456,789.00

Invalid Comma Placement

A comma or group separator cannot appear to the right of a decimal character or period in a number format model.

SELECT TO_CHAR(12345, 'fm99D999G00')
FROM DUAL;

Result:

Error report -
ORA-01481: invalid number format model