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)

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

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