Sql Query to add comma in price using SQL Server

Introduction:

In my previous article I have explained about How to get selected items text  using Javascript. In this article I am going to explain about how to format numbers in indian(1,23,45,67,890) and US(1,234,567,890) style in SQL.

Explanation:

To format the numbers in indian style I have wrote two sql functions. First function will add the comma(,) after 3 digits from right and the second function will add comma after two two digits from the 3^rd digit onward from the right.

Below are the two functions

CREATE FUNCTION [dbo].[fn_FormatWithCommas]    

(   

    -- Add the parameters for the function here   

    @value varchar(50)   

)   

RETURNS varchar(50)   

AS   

BEGIN   

-- Declare the return variable here   

DECLARE @WholeNumber varchar(50) = NULL, @Decimal varchar(10) = '', @CharIndex int = charindex('.', @value)   

   

IF (@CharIndex > 0)

SELECT @WholeNumber = SUBSTRING(@value, 1, @CharIndex-1), @Decimal = SUBSTRING(@value, @CharIndex, LEN(@value))   

ELSE   

   SET @WholeNumber = @value   

   

IF(LEN(@WholeNumber) > 2)   

SET @WholeNumber = dbo.fn_FormatWithCommas2(SUBSTRING(@WholeNumber, 1, LEN(@WholeNumber)-3)) + ',' + RIGHT(@WholeNumber, 3)   

    

-- Return the result of the function   

RETURN @WholeNumber + @Decimal   

   

END 

GO

CREATE FUNCTION [dbo].[fn_FormatWithCommas2]    

(   

    -- Add the parameters for the function here   

    @value varchar(50)   

)   

RETURNS varchar(50)   

AS   

BEGIN   

    -- Declare the return variable here   

DECLARE @WholeNumber varchar(50) = NULL, @Decimal varchar(10) = '', @CharIndex int = charindex('.', @value)   

   

IF (@CharIndex > 0)   

SELECT @WholeNumber = SUBSTRING(@value, 1, @CharIndex-1), @Decimal =     SUBSTRING(@value, @CharIndex, LEN(@value))   

ELSE   

    SET @WholeNumber = @value   

   

IF(LEN(@WholeNumber) > 2)   

SET @WholeNumber = dbo.fn_FormatWithCommas2(SUBSTRING(@WholeNumber, 1, LEN(@WholeNumber)-2)) + ',' + RIGHT(@WholeNumber, 2)   

-- Return the result of the function   

RETURN @WholeNumber + @Decimal   

   

END

Below is the sample usage of the code.

DECLARE @price INT

SET @price=1234567890

SELECT dbo.fn_FormatWithCommas(1234567890)

Below is the code to format numbers in US style

SELECT CONVERT(varchar, CAST(987654321 AS money), 1)

Do you like this article? Help us to improve. Please post your comments below.