Convert Indian Currency Format in SQL server

Convert Indian Currency Format in SQL server

Use this Function It will convert and returns the Indian currency format

-- local settings may produce this for you
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
alter FUNCTION dbo.IndianDecimalFormat
(
@Currency decimal(11,2)
)
RETURNS varchar(15)
AS
BEGIN
DECLARE @tCurr varchar(12)
,@RetVal varchar(16)

SELECT @tCurr = CAST(@Currency AS varchar(12))
,@RetVal = ''

;WITH IndianCurrency (pos, ln, ord)
AS
(
SELECT 1, 2, 5
UNION ALL SELECT 4, 3, 4
UNION ALL SELECT 7, 2, 3
UNION ALL SELECT 9, 2, 2
UNION ALL SELECT 11, 2, 1
), Split (Number, ord)
AS
(
SELECT REVERSE(SUBSTRING(REVERSE(@tCurr), pos, ln))
,ord
FROM IndianCurrency
)
SELECT @RetVal = @RetVal + Number + CASE WHEN ord = 4 THEN '.' ELSE ',' END
FROM Split
WHERE LEN(Number) > 0
ORDER BY ord

RETURN LEFT(@RetVal, LEN(@RetVal) - 1)
END
GO

--select dbo.IndianDecimalFormat(454121)

O/P: 4,54,121.00