fixed number format with different lengths in Oracle

I need help with a Oracle Query I have a query:

scenario 1: select to_char(‘1737388250’,what format???) from dual;

expected output: 173,7388250

scenario 2: select to_char(‘173738825034’,what format??) from dual;

expected output: 173,738825034

scenario 3: select to_char(‘17373882’,what format??) from dual;

expected output: 173,73882

I need a query to satify all above scenarios? Can some one help please?

Answer

It is possible to get the desired result with a customized format model given to to_char; I show one example below. However, any solution along these lines is just a hack (a solution that should work correctly in all cases, but using features of the language in ways they weren’t intended to be used).

Here is one example – this will work if your “inputs” are positive integers greater than 999 (that is: at least four digits).

with
  sample_data (num) as (
    select 1737388250 from dual union all
    select      12338 from dual
  )
select num, to_char(num, rpad('fm999G', length(num) + 3, '9')) as formatted
from   sample_data
;

       NUM  FORMATTED   
----------  ------------
1737388250  173,7388250 
     12338  123,38

This assumes comma is the “group separator” in nls_numeric_characters; if it isn’t, that can be controlled with the third argument to to_char. Note that the format modifier fm is needed so that no space is prepended to the resulting string; and the +3 in the second argument to rpad accounts for the extra characters in the format model (f, m and G).