Handy function for formatting numbers in SQL. It can be customized easily according to your need.
ALTER FUNCTION [dbo].[ufn_formatNumber](@num float)
RETURNS varchar(20)
AS
-- Returns the number formatted to K,M or () in case of negative numbers
BEGIN
Declare @out varchar(20)
IF (@num IS NULL)
SET @out = 0;
IF (abs(@num)<=10000) -- Thousand Handler
SET @out='$' +cast(abs(round(@num,2)) as varchar)
IF (abs(@num)>10000) -- Thousand Handler
SET @out='$' +cast(abs(round(@num/1000,2)) as varchar)+' K'
IF (abs(@num)>1000000) -- Million Handler
SET @out='$' +cast(abs(round(@num/1000000,2)) as varchar)+' M'
IF (@num<0)
SET @out= '('+@out +')'
RETURN @out;
END;
GO
ALTER FUNCTION [dbo].[ufn_formatNumber](@num float)
RETURNS varchar(20)
AS
-- Returns the number formatted to K,M or () in case of negative numbers
BEGIN
Declare @out varchar(20)
IF (@num IS NULL)
SET @out = 0;
IF (abs(@num)<=10000) -- Thousand Handler
SET @out='$' +cast(abs(round(@num,2)) as varchar)
IF (abs(@num)>10000) -- Thousand Handler
SET @out='$' +cast(abs(round(@num/1000,2)) as varchar)+' K'
IF (abs(@num)>1000000) -- Million Handler
SET @out='$' +cast(abs(round(@num/1000000,2)) as varchar)+' M'
IF (@num<0)
SET @out= '('+@out +')'
RETURN @out;
END;
GO