Mar 30, 2011

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.


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

    Choose :
  • OR
  • To comment
No comments:
Write Comments

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio