number formatting 9’999.00

I’d like to format a number in a SQL select like this

9'999'999.00

How can I do this?

It works for 9,999,999 with the following command

to_char(9999999, 'FM9G999G999D999', 'NLS_NUMERIC_CHARACTERS='',.')

Answer

You are almost there. Just replace group separator (.) with a single quote (') and you are good to go.

select to_char(9999999, 'FM9G999G999D00', 
               q'[NLS_NUMERIC_CHARACTERS='.'']') as res
  from dual

result:

RES          
-------------
9'999'999.00 

1 row selected.