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.
Comments
Post a Comment