format decimals and comma to numbers retrieved

I have a column in my table which showing an amount. The 开发者_JAVA百科amount is varying from one column to another and they are more than 15 digits.

What is the best way to format the number to show commas and decimal points?

My query is

select amount from ccamounts

How can I format the number

205511892078

to show as

205,511,892,078

and if there is a radix point it will also appear.

I believe you can use TO_CHAR to do this, the issue is that this is just a formatting function within SQL. It requires that your number is always going to be in the same format.

taking the example above you could do

TO_CHAR('205511892078', '999,999,999,999')

and this would format the number as you have specified, with a decimal place this can be done aswell but the decimal needs to be specified:

TO_CHAR('20551189207842', '999,999,999,999.99')

which would give you 205,511,892,078.42

I think if the field length is going to vary sql will just ignore anything that doesn't fit into the format string (It's a mask). Perhaps you want to consider formatting the number in this case on whichever front end you may be using?

I would format the number in the UI / Reporting tool / Presentation layer not Oracle

but if you MUST format it in oracle try:

SELECT 
    CASE WHEN INSTR( TO_CHAR(205511892078),'.')>0 THEN
        TO_CHAR(205511892078 ,'999,999,999,999.99')
    ELSE
        TO_CHAR(205511892078 ,'999,999,999,999') 
    END        
FROM DUAL

this will return the number as a string.

declare @d3 decimal (10, 2)

set @d3 = 12309809.5494

SELECT convert(varchar(15),cast(CAST(ROUND(@d3,2,1) AS DECIMAL (30,2)) as money),1) as Value 

SELECT CAST(ROUND(convert(varchar(30), cast(@d3 as money),2),2,1) AS DECIMAL (30,2)) as Value 

Output:

12,309,809.55

12309809.55