plsql custom numeric format

Question

I'm trying to format a numeric value always in the format "9 999,99" (notice the space).

The problem is that oracle does not provide me a a numeric mask for getting this format.

The closest I get is with to_char(value,'99990,99'), But no way of putting that space after the third digit.

some examples of what I would like to get 1345.67 -> 1 345,67 12356.00 -> 12 356,00 0.56 -> 0,56

How could I do this, maybe using a regular expresion?? any idea?

Answer 1

SQL> alter session set nls_numeric_characters = ', '
  2  ;

Session altered.

SQL> select to_char(999999/100, '9G999D99')
  2    from dual;

TO_CHAR(9
---------
 9 999,99

You can also specify the NLS_NUMERIC_CHARACTERS setting in the TO_CHAR statement rather than setting it at the session level

  1  select to_char(999999/100, '9G999D99', 'nls_numeric_characters='', '' ')
  2*   from dual
SQL> /

TO_CHAR(9
---------
 9 999,99