formatting a number to a display string

Question

Converting a number to a string for example 20000000.00 like this 20 000 000.00 with gap. Please help me

Answer 1

One option is to use to_char function with nls_numeric_characters set to a dot (decimal point) and space (group separator):

SQL> with test (col) as
  2    (select 20000000.00 from dual union all
  3     select 12345.324   from dual)
  4  select to_char(col, '999G999G999G990D00', 'nls_numeric_characters=''. ''') result
  5  from test;

RESULT
-------------------
      20 000 000.00
          12 345.32

SQL>