Sql Query to add comma in price using SQL Server

By
Advertisement


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 3rd 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.

0 comments:

Post a Comment

Online Casino