SQL Function that convert numerical currency to String format.

Ex. If you pass the

SELECT dbo.fn_Convert_NumToWords(1268.00, 'Y')
1268 then it return One Thousand Two Hundred Sixty Eight Rupees.SELECT dbo.fn_Convert_NumToWords(1268.50, 'Y')
1268.50 then it return One Thousand Two Hundred Sixty Eight Rupees and Fifty Paise
If you want only round fig. in calculation then
SELECT dbo.fn_Convert_NumToWords(1268.50, 'N')
1268.50 than it return One Thousand Two Hundred Sixty Eight Rupees.
/****** Object: UserDefinedFunction [dbo].[fn_Convert_NumToWords] Script Date: 07/23/2012 14:37:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- SELECT dbo.fn_Convert_NumToWords(1268.00, 'Y')
CREATE FUNCTION [dbo].[fn_Convert_NumToWords]
(
@Number NUMERIC(18, 2),
 @CPaise CHAR(1)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @StrNumber VARCHAR(10),
 @SLacs CHAR(2),
 @SThou CHAR(2),
 @SHun CHAR(2)
DECLARE @STenUnt CHAR(2),
 @STen CHAR(2),
 @SUnt CHAR(2),
 @SDecimal CHAR(2)
DECLARE @ILacs INT,
 @IThou INT,
 @IHun INT,
 @ITenUnt INT,
 @ITen INT,
 @IUnt INT,
 @IDecimal INT
DECLARE @SNumToWords VARCHAR(100),
 @numberInWord VARCHAR(10)
SELECT @StrNumber = REPLICATE('0', 10 - LEN(LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))))
+ LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))
SELECT @SNumToWords = ''
 IF LEN(LTRIM(RTRIM(CONVERT(VARCHAR, @Number)))) > 4
BEGIN
 SELECT @SLacs = SUBSTRING(@StrNumber, 1, 2)
 SELECT @ILacs = CONVERT(INT, @SLacs)
IF @ILacs > 0
BEGIN
 SELECT @STen = SUBSTRING(@StrNumber, 1, 1)
SELECT @SUnt = SUBSTRING(@StrNumber, 2, 1)
 IF CONVERT(INT, @STen) = 1
BEGIN
 SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 1, 2))
SELECT @IUnt = 0
END
 ELSE
 BEGIN
 SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
 END
IF @ITen > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM dbo.Gen_NumberInWords_Master
WHERE WordToNum_Number = @ITen

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
IF @IUnt > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @IUnt

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
SELECT @SNumToWords = @SNumToWords + ' Lacs'
 END
SELECT @SThou = SUBSTRING(@StrNumber, 3, 2)
SELECT @IThou = CONVERT(INT, @SThou)
 IF @IThou > 0
BEGIN
 SELECT @STen = SUBSTRING(@StrNumber, 3, 1)
 SELECT @SUnt = SUBSTRING(@StrNumber, 4, 1)
IF CONVERT(INT, @STen) = 1
BEGIN
 SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 3, 2))
 SELECT @IUnt = 0
END
 ELSE
 BEGIN
 SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
 END
IF @ITen > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @ITen

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
IF @IUnt > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @IUnt

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
SELECT @SNumToWords = @SNumToWords + ' Thousand '
 END
SELECT @SHun = SUBSTRING(@StrNumber, 5, 1)
SELECT @IHun = CONVERT(INT, @SHun)
IF @IHun > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @IHun

SELECT @SNumToWords = @SNumToWords + @numberInWord + ' Hundred'
 END
SELECT @STenUnt = SUBSTRING(@StrNumber, 6, 2)
SELECT @ITenUnt = CONVERT(INT, @STenUnt)
IF @ITenUnt > 0
BEGIN
 SELECT @STen = SUBSTRING(@StrNumber, 6, 1)
SELECT @SUnt = SUBSTRING(@StrNumber, 7, 1)
IF CONVERT(INT, @STen) = 1
BEGIN
 SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 6, 2))
SELECT @IUnt = 0
END
 ELSE
 BEGIN
 SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
 END
IF @ITen > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @ITen

--Select @SNumToWords = @SNumToWords + Space(1) + @numberInWord
SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
IF @IUnt > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @IUnt


SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
 END
SELECT @SNumToWords = @SNumToWords + SPACE(1) + 'Rupees'
 END
 ELSE
 BEGIN
 SELECT @SLacs = SUBSTRING(LTRIM(RTRIM(CONVERT(VARCHAR, @Number))), 1, 1)
 SELECT @ILacs = CONVERT(INT, @SLacs)
IF @ILacs > 0
AND @ILacs <> 1
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @ILacs

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord + SPACE(1) +
'Rupees'
 END
 ELSE
 BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @ILacs

SELECT @SNumToWords = @SNumToWords + @numberInWord + SPACE(1) + 'Rupee'
 END
 END
IF @CPaise = 'Y'
 BEGIN
 SELECT @SDecimal = SUBSTRING(@StrNumber, 9, 2)
SELECT @IDecimal = CONVERT(INT, @SDecimal)
IF @IDecimal > 0
BEGIN
 SELECT @SNumToWords = @SNumToWords + ' and'
SELECT @STen = SUBSTRING(@SDecimal, 1, 1)
SELECT @SUnt = SUBSTRING(@SDecimal, 2, 1)
IF CONVERT(INT, @STen) = 1
BEGIN
 SELECT @ITen = CONVERT(INT, SUBSTRING(@StrNumber, 9, 2))
SELECT @IUnt = 0
END
 ELSE
 BEGIN
 SELECT @ITen = CONVERT(INT, @STen) * 10
SELECT @IUnt = CONVERT(INT, @SUnt)
 END
IF @ITen > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @ITen

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
IF @IUnt > 0
BEGIN
 SELECT @numberInWord = ''
SELECT @numberInWord = WordToNum_InWords
FROM Gen_NumberInWords_Master
WHERE WordToNum_Number = @IUnt

SELECT @SNumToWords = @SNumToWords + SPACE(1) + @numberInWord
END
SELECT @SNumToWords = @SNumToWords + SPACE(1) + 'Paise'
 END
 END
 RETURN LTRIM(RTRIM(@SNumToWords))
END