Thousand separator Oracle

Question

I can use this to get desired format:

SELECT TO_CHAR(12345,'99G999') "Amount" FROM DUAL;
SELECT TO_CHAR(123456,'999G999') "Amount" FROM DUAL;
SELECT TO_CHAR(1234567,'9G999G999') "Amount" FROM DUAL; 

Is there a method where you don't have to check the length like this?:

select case
when length(my_number) = 5 then TO_CHAR(my_number,'99G999')
when length(my_number) = 6 then TO_CHAR(my_number,'999G999')

Answer 1

Using 9 in the model (as opposed to 0) means you don't get leading zeros, so you can use the same model for all of those:

SELECT TO_CHAR(12345,'9G999G999') "Amount" FROM DUAL;

Amount    
----------
    12,345

SELECT TO_CHAR(123456,'9G999G999') "Amount" FROM DUAL;

Amount    
----------
   123,456

SELECT TO_CHAR(1234567,'9G999G999') "Amount" FROM DUAL; 

Amount    
----------
 1,234,567

You just need enough 9s (and Gs) for the longest value you expect to handle.

If you don't want the leading spaces - including the one that's there in all of those as a placeholder for a potential minus sign for negative values - you can add the FM 'fill mode' format model modifier:

SELECT TO_CHAR(12345,'FM999G999G999G999') "Amount" FROM DUAL;

Amount          
----------------
12,345

SELECT TO_CHAR(123456,'FM999G999G999G999') "Amount" FROM DUAL;

Amount          
----------------
123,456

SELECT TO_CHAR(1234567,'FM999G999G999G999') "Amount" FROM DUAL; 

Amount          
----------------
1,234,567