Currency format with commas in sql server

select convert (varchar(20), convert(money, 9123456789.00), 1 )

output::9,123,456,789.00

If u want to format into indain rupees format here is my suggestion ::


create function dbo.RupieFormat(@v float)
returns varchar(200)
as
begin
declare @res varchar(50)
declare @p1 varchar(40)
declare @p2 varchar(10)

set @res = replace(convert (varchar(20), convert(money, @v), 3 ) , ',','')
set @p1 = left(@res, charindex('.', @res)-1)
set @p2 = substring(@res, charindex('.', @res), 10)

set @res = right(@p1, 3) + @p2

if(len(@p1)>2)
set @p1 = left(@p1, len(@p1)-3)
else if(len(@p1)>1)
set @p1 = left(@p1, len(@p1)-2)
else
set @p1 = left(@p1, len(@p1)-1)

while (@p1<>'')
begin
set @res = right(@p1, 2) + ',' + @res
if (len(@p1) > 2)
set @p1 = left(@p1, len(@p1)-2)
else
set @p1= ''
end

return(@res)
end


Eg::

select dbo.RupieFormat(price) as price from products

or

select dbo.RupieFormat(9123456789.12)

output:9,12,34,56,789.00