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