Format Number in SQL Server

To format a number in custom format like 99,99,999.00 there is no inbuilt function prior to SQL Server 2012, however same is available in SQL Sever 2012 onwards.

• Format Function in SQL Server 2012

In this article, I am sharing a user defined function to format the number in custom format for prior versions.

The function takes two parameters

• Number to be formatted.  Ex : (123456789.99999)
• Format. Ex : (99,99,99,9999,99)

and returns

• formatted number as output Ex : (12,34,56,789.99)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

CREATE FUNCTION Fnformat(       @pNumber NUMERIC(16, 5)     , @pFormat VARCHAR(50) ) RETURNS VARCHAR(50) AS BEGIN     DECLARE   @lStr1      VARCHAR(50)             , @lCtrS      SMALLINT             , @lCtrE      SMALLINT             , @lChr       CHAR             , @lFormat    VARCHAR(50)             , @lPosStr    SMALLINT             , @lPosFormat SMALLINT             , @lAfterDot  VARCHAR(10)             , @lBeforeDot VARCHAR(50)                     SET @lStr1 = CAST(@pNumber AS VARCHAR)     SET @lPosStr = Charindex('.', Reverse(@lStr1)) - 1     SET @lPosFormat = Charindex('.', Reverse(@pFormat))     IF @lPosFormat > 0         SET @lPosFormat = @lPosFormat - 1     IF @lPosStr < @lPosFormat         SET @lAfterDot = RIGHT(@lStr1,@lPosStr)+Replicate('0', @lPosFormat-@lPosStr)     ELSE         SET @lAfterDot = LEFT(RIGHT(@lStr1, @lPosStr), @lPosFormat)     IF @lPosStr > 0         SET @lBeforeDot = Reverse(LEFT(@lStr1, Charindex('.', @lStr1) - 1))     ELSE         SET @lBeforeDot = Reverse(@lStr1)     IF @lPosFormat > 0         SET @lFormat = Reverse(Substring(@pFormat, 1, Charindex('.', @pFormat)- 1))     ELSE         SET @lFormat = Reverse(@pFormat)       SET @lCtrS = 0     SET @lCtrE = Len(@lFormat)     WHILE @lCtrS < @lCtrE     BEGIN         SET @lCtrS = @lCtrS + 1         SET @lChr = Substring(@lFormat, @lCtrS, 1)         IF @lChr = ',' AND Len(@lBeforeDot) >= @lCtrS             SET @lBeforeDot = LEFT(@lBeforeDot, @lCtrS - 1) + ',' + Substring(@lBeforeDot, @lCtrS, 100)     END       IF @lPosStr > 0 AND @lPosFormat > 0         SET @lStr1 = Reverse(@lBeforeDot) + '.' + @lAfterDot     ELSE         SET @lStr1 = Reverse(@lBeforeDot)       RETURN @lStr1 END

Let's test the function

?

1 2 3 4

SELECT dbo.fnFormat(12345.99, '99,999.99') as [Formatted Value] SELECT dbo.fnFormat(1234567.99, '9,999,999.99') as [Formatted Value] SELECT dbo.fnFormat(123456789.99, '99,99,99,999.99') as [Formatted Value] SELECT dbo.fnFormat(123456789.99, '999,999,999.99') as [Formatted Value]

OUTPUT