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.99Create 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.SEPARATETHOUSANDNUM
(
@.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.SEPAREATENUMBERS(23565) AS CHANGEDCOLUMN
gives 23,565
SELECT DBO.SEPAREATENUMBERS(2365) AS CHANGEDCOLUMN
gives 2,365
So use
Select DBO.SEPARATETHOUSANDNUM(TotalAmount) from Table1
Quote:
Originally Posted by 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
like this
select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser|||
Quote:
Originally Posted by sukeshchand
I got an another easy solution for that and no need for any functions
like this
select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser
Excellent! Thanks for posting the solution!|||
Quote:
Originally Posted by sukeshchand
I got an another easy solution for that and no need for any functions
like this
select convert(varchar(50),convert(money,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?|||
Quote:
Originally Posted by mjv
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.
