quote:

---

Originally posted by vinu

hi... to all
i want to format the number in indian currency format
e.g(12,12,12,123.00) and also in words
such as twelve crore twelve lakhs twleve thousand one hundred and twenty three rupees only.
if there is a valuve 0.5 then five paise only.
help me...

---

CREATE TABLE NumberWords
(
	Number smallint NOT NULL
		PRIMARY KEY CLUSTERED
	,NumberWord varchar(31) NOT NULL
)
;WITH FirstTwenty (Number, Word)
AS
(
	SELECT 0, NULL
	UNION ALL SELECT 1, 'One'
	UNION ALL SELECT 2, 'Two'
	UNION ALL SELECT 3, 'Three'
	UNION ALL SELECT 4, 'Four'
	UNION ALL SELECT 5, 'Five'
	UNION ALL SELECT 6, 'Six'
	UNION ALL SELECT 7, 'Seven'
	UNION ALL SELECT 8, 'Eight'
	UNION ALL SELECT 9, 'Nine'
	UNION ALL SELECT 10, 'Ten'
	UNION ALL SELECT 11, 'Eleven'
	UNION ALL SELECT 12, 'Twelve'
	UNION ALL SELECT 13, 'Thirteen'
	UNION ALL SELECT 14, 'Fourteen'
	UNION ALL SELECT 15, 'Fifteen'
	UNION ALL SELECT 16, 'Sixteen'
	UNION ALL SELECT 17, 'Seventeen'
	UNION ALL SELECT 18, 'Eighteen'
	UNION ALL SELECT 19, 'Nineteen'
), Tys(Number, Word)
AS
(
	SELECT 20, 'Twenty'
	UNION ALL SELECT 30, 'Thirty'
	UNION ALL SELECT 40, 'Fourty'
	UNION ALL SELECT 50, 'Fifty'
	UNION ALL SELECT 60, 'Sixty'
	UNION ALL SELECT 70, 'Seventy'
	UNION ALL SELECT 80, 'Eighty'
	UNION ALL SELECT 90, 'Ninety'
), FirstHundred(Number, Word)
AS
(
	SELECT Number, Word
	FROM FirstTwenty
	UNION ALL
	SELECT T.Number + F.Number
		,T.Word + COALESCE(' ' + F.Word, '')
	FROM tys T
		CROSS JOIN FirstTwenty F
	WHERE F.Number < 10
), FirstThousand(Number, Word)
AS
(
	SELECT *
	FROM FirstHundred
	UNION ALL
	SELECT (T.Number * 100) + H.Number
		,T.Word + ' Hundred' + COALESCE(' and ' + H.Word, '')
	FROM FirstTwenty T
		CROSS JOIN FirstHundred H
	WHERE T.Number BETWEEN 1 AND 9
) 
INSERT INTO NumberWords
SELECT Number, Word
FROM FirstThousand
WHERE Number > 0

SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.IndianCurrencyInWords
(
	@Currency decimal(11,2)
)
RETURNS varchar(255)
AS
BEGIN
	DECLARE @tCurr varchar(12)
		,@RetVal varchar(255)

	SELECT @tCurr = CAST(@Currency AS varchar(12))
		,@RetVal = ''
	
	;WITH IndianCurrency (pos, ln, ord, sng, plr)
	AS
	(
		SELECT 1, 2, 5, 'Paisa', 'Paise'
		UNION ALL SELECT 4, 3, 4, 'Rupee', 'Rupees'
		UNION ALL SELECT 7, 2, 3, 'Thousand', 'Thousand'
		UNION ALL SELECT 9, 2, 2, 'Lakh', 'Lakhs'
		UNION ALL SELECT 11, 2, 1, 'Crore', 'Crores'
	), Split (Number, ord, sng, plr)
	AS
	(
		SELECT REVERSE(SUBSTRING(REVERSE(@tCurr), pos, ln))
			,ord, sng, plr
		FROM IndianCurrency
	), OrdWord (ord, Word)
	AS
	(
		SELECT ord
			,CASE
				WHEN @Currency >= 1 AND ord = 5 THEN 'and '
				WHEN W.Number < 100 AND ord = 4 THEN 'and '
				ELSE ''
			END
			+ CASE WHEN @Currency >= 1 AND ord = 4 AND W.NumberWord IS NULL THEN 'Rupees' ELSE '' END
			+ COALESCE(W.NumberWord + ' ' + CASE WHEN W.Number = 1 and @Currency < 2 THEN S.sng ELSE S.plr END, '')
		FROM Split S
			LEFT JOIN NumberWords W
				ON S.Number = W.Number
		WHERE LEN(S.Number) > 0
	)
	SELECT @RetVal = @RetVal + COALESCE(' ' + Word, '')
	FROM OrdWord B
	WHERE LEN(Word) > 0
	ORDER BY ord

	RETURN @RetVal
END
GO

-- local settings may produce this for you
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE 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