How to format numbers in SQL Query

[sukeshchand]

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
----------------------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99

[vijaii]

Create the below function and use it as said below.

/*This function is only for thousand separator for numbers with length 5 or 4*/
CREATE FUNCTION DBO.SEPARATETHO USANDNUM
(
@STRVALUE VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @STRRETURNVALUE VARCHAR(8000)
SELECT @STRRETURNVALUE = CASE LEN(@STRVALUE)
WHEN 5 THEN LEFT(@STRVALUE, 2)+ ','+ RIGHT(@STRVALUE ,3)
WHEN 4 THEN LEFT(@STRVALUE, 1)+ ','+ RIGHT(@STRVALUE ,3)
ELSE @STRVALUE END
RETURN @STRRETURNVALUE
END



SELECT DBO.SEPAREATENU MBERS(23565) AS CHANGEDCOLUMN

gives 23,565

SELECT DBO.SEPAREATENU MBERS(2365) AS CHANGEDCOLUMN

gives 2,365

So use
Select DBO.SEPARATETHO USANDNUM(TotalA mount) from Table1

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
----------------------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99

[sukeshchand]

I got an another easy solution for that and no need for any functions

like this

select convert(varchar (50),convert(mo mey,TotalAmount ),1) from BillMaser

[Motoma]

I got an another easy solution for that and no need for any functions

like this

select convert(varchar (50),convert(mo mey,TotalAmount ),1) from BillMaser

Excellent! Thanks for posting the solution!

[mjv]

I got an another easy solution for that and no need for any functions

like this

select convert(varchar (50),convert(mo ney,TotalAmount ),1) from BillMaser

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?

[ck9663]

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?

try adding precision on your convert function.

actually, although this is feasible in the database/back-end, i believe this can be better be handled in the front-end.

[Vis]

i have a solution similar to the post above, this function will convert any number to ones with commas upto 1 Trillion .....

i.e 58,895158456.02 5 = 58,895,158,456. 025

You can change the precision of the decimal as you want, but u will need to make a slight modification to each of the cases. Currently the function will take any decimal 18,3.


CREATE FUNCTION DBO.SEPARATETHO USANDNUM
(
@INVALUE decimal(18,3)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @STRRETURNVALUE VARCHAR(MAX)
DECLARE @STRVALUE VARCHAR(MAX)
SET @STRVALUE = convert(VARCHAR (MAX), @INVALUE)

SELECT @STRRETURNVALUE =
CASE
WHEN @INVALUE < 1000 THEN @STRVALUE
WHEN @INVALUE < 1000000 THEN convert(VARCHAR (MAX),convert(d ecimal(18,0), @INVALUE / 1000)) + ',' + right('000' + convert(VARCHAR (MAX),(@INVALUE % 1000)), 7)
WHEN @INVALUE < 1000000000 THEN left(convert(VA RCHAR(MAX),conv ert(decimal(18, 0), @INVALUE / 1000)), len(@INVALUE)-10) + ',' + right(convert(V ARCHAR(MAX),con vert(decimal(18 ,0), @INVALUE / 1000)),3) + ',' + right('000' + convert(VARCHAR (MAX),(@INVALUE % 1000)), 7)
WHEN @INVALUE < 1000000000000 THEN left(convert(VA RCHAR(MAX),conv ert(decimal(18, 0), @INVALUE / 1000)), len(@INVALUE)-13) + ',' + right(left(conv ert(VARCHAR(MAX ),convert(decim al(18,0), @INVALUE / 1000)), len(@INVALUE)-10),3) + ',' + right(convert(V ARCHAR(MAX),con vert(decimal(18 ,0), @INVALUE / 1000)),3) + ',' + right('000' + convert(VARCHAR (MAX),(@INVALUE % 1000)), 7)
WHEN @INVALUE < 100000000000000 0 THEN left(convert(VA RCHAR(MAX),conv ert(decimal(18, 0), @INVALUE / 1000)), len(@INVALUE)-16) + ',' + right(left(conv ert(VARCHAR(MAX ),convert(decim al(18,0), @INVALUE / 1000)), len(@INVALUE)-13),3) + ',' + right(left(conv ert(VARCHAR(MAX ),convert(decim al(18,0), @INVALUE / 1000)), len(@INVALUE)-10),3) + ',' + right(convert(V ARCHAR(MAX),con vert(decimal(18 ,0), @INVALUE / 1000)),3) + ',' + right('000' + convert(VARCHAR (MAX),(@INVALUE % 1000)), 7)
ELSE @STRVALUE END

RETURN @STRRETURNVALUE
END

I hope this is helpful

[jtipsword]

Round Funtion

There is a built in function called round

ROUND(Number to Round, Decimal Places)

So

ROUND(1.2358963 9,4) would return 1.2359