mister.magoo (9/5/2013)
Hi, this should help:
CREATE FUNCTION FormatLakhCrores (@number SQL_VARIANT,@conversion smallint)
RETURNS TABLE
WITH SCHEMABINDING
AS
--= Function to format a number using the Lakh/Crores style
--= Written 06 Sept 2013 Mister Magoo
--= Permission granted for public re-use for any purpose except derision
--= Parameter @number : pass in a numeric/decimal/integer/money type
--= Parameter @conversion : pass in a suitable conversion style for the CONVERT function - if in doubt pass 0 (zero) (a value of 2 with the money data type will force 4 decimal places)
--= Returns a varchar value containing the formatted number
RETURN
-- the returned value is built up by stripping the number down and inserting commas where required, then concatenating the digits
SELECT
-- because the comma insertion works from right to left, we need to reverse the result to see the required string
REVERSE((
-- the case statement figures out where to insert a comma
-- we need a comma before the last three digits before the decimal point
-- but we are working from right to left, so they are the first three after the point.
-- subsequently we need a comma every two digits
SELECT CASE
-- all digits after the last/first comma are in this group
WHEN N-scale < 5
THEN ''
-- the last/first comma goes here
WHEN N-scale = 5
THEN ','
-- capture every second digit here
WHEN (N-scale) % 2 = 1
THEN ','
ELSE ''
END
-- and grab the digit at this position in the string
+ SUBSTRING(string, LEN(string) - N+1, 1)
FROM
-- Build an inline tally table which contains 49 numbers - more than enough for a decimal(38)
(
SELECT ROW_NUMBER() OVER(ORDER BY @conversion) -- the order by is irrelevant
FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) a7(N)
,(SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) b7(N)
) AS Tally49(N)
CROSS APPLY
-- Use a cross apply construct to alias a couple of expressions
(
SELECT
-- our plain formatted number with any pre-existing commas stripped
replace(convert(VARCHAR(1000), @number, @conversion),',','') AS string
-- figure where the decimal point is - if any is present
,CHARINDEX('.',reverse(replace(convert(VARCHAR(1000), @number, @conversion),',','')))-1
) x(string, scale)
-- only use as much of the Tally table as we need
WHERE N< = len(string)
ORDER BY N
-- and concatenate the results using for xml path()
FOR XML PATH('')
)) AS FormattedNumber
Use it like this for "money" :
declare @test-2 money = 1000000000;
select FormattedNumber
from FormatLakhCrores(@test,0)
Or from a table:
select some_column,FormattedNumber
from SomeTable
cross apply dbo.FormatLakhCrores(some_column,0)
Thanks alot:)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply