Wednesday 27 April 2011

SQL Server user defined function code for formatting numbers to have Comma seperator

Hi,

SQL Server does't have any inbuild function to put thousand Comma sepeator. You can use the Below MS SQL server user function or Code for formatting Numeric Values to have Comma seperator.

View Optimal Alternate Approach

CREATE FUNCTION  NumberCommaFormat(@var DECIMAL(18,10) )
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @decimal VARCHAR(MAX)='' 
DECLARE @num INT, @rem VARCHAR(MAX)
DECLARE @final_num VARCHAR(MAX)='' , @Output VARCHAR(MAX)='' 
DECLARE @some_char VARCHAR(MAX) = @var
SET @decimal= SUBSTRING(@some_char,CHARINDEX('.',@var ,1), LEN(@var) )

IF( SUBSTRING(@decimal,4,LEN(@decimal)) = '00000000' )
BEGIN

SET @Output = CONVERT(VARCHAR,CAST(@var AS MONEY),1) ;
END

ELSE
BEGIN
IF(@var > 1)
SELECT @num=SUBSTRING(@some_char, 1,CHARINDEX('.',@var ,1)-1 )

WHILE (@num >0 )
BEGIN

SET @rem = @num%1000

SET @final_num= ','+@rem +@final_num ;
SET @num =@num/1000
END 
IF(@var > 1)
SET @Output= STUFF(@final_num,1,1,'') + @decimal

ELSE
SET @Output= '0.'+ @decimal

END

RETURN (@Output);
END



0 comments:

Post a Comment