CODEWITHSUNDEEP
sqloracle-database
maas
maas

Reputation: 1

format decimals and comma to numbers retrieved

I have a column in my table which showing an amount. The 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.

Upvotes: 0

Views: 4963

Answers (3)

vik
vik

Reputation: 7

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

Upvotes: -1

Kevin Burton
Kevin Burton

Reputation: 11924

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.

Upvotes: 1

DaeDaLuS_015
DaeDaLuS_015

Reputation: 110

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?

Upvotes: 1

Related Questions