In this Articles ,I would like to show simple
function to Convert Indian Money to word format in Indian money .
Pass the input string in function and get the value in word according to Indian rupee.
For example the input value 9,00,000 (Indian Money) The output as follow:
Input value: 9,00,000
Output : 9 Lacs
Source Code
@TOTALAMOUNT varchar(max)
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @AMOUNTWORD varchar(500)
DECLARE @LENGTH VARCHAR(200)=0
DECLARE @SECONDWORD VARCHAR(200)
SET @LENGTH=(SELECT LEN(@TOTALAMOUNT))
IF(@LENGTH=4)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Thousand'
END
ELSE IF(@LENGTH=5)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + 'Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Thousand'
END
ELSE IF(@LENGTH=6)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Lacs'
END
IF(@LENGTH=7)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Lacs'
END
ELSE IF(@LENGTH=8)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Crore'
END
ELSE IF(@LENGTH=9)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Crore'
END
ELSE IF(@LENGTH=10)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Arab'
END
ELSE IF(@LENGTH=11)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Arab'
END
RETURN @AMOUNTWORD
END
Pass the input string in function and get the value in word according to Indian rupee.
For example the input value 9,00,000 (Indian Money) The output as follow:
Input value: 9,00,000
Output : 9 Lacs
Source Code
CREATE FUNCTION [dbo].[AmountToWords]
(@TOTALAMOUNT varchar(max)
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @AMOUNTWORD varchar(500)
DECLARE @LENGTH VARCHAR(200)=0
DECLARE @SECONDWORD VARCHAR(200)
SET @LENGTH=(SELECT LEN(@TOTALAMOUNT))
IF(@LENGTH=4)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Thousand'
END
ELSE IF(@LENGTH=5)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + 'Thousand'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Thousand'
END
ELSE IF(@LENGTH=6)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Lacs'
END
IF(@LENGTH=7)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Lacs'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Lacs'
END
ELSE IF(@LENGTH=8)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Crore'
END
ELSE IF(@LENGTH=9)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Crore'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Crore'
END
ELSE IF(@LENGTH=10)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00)
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1))+'.'+@SECONDWORD + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,1)) + ' Arab'
END
ELSE IF(@LENGTH=11)
BEGIN
SET @SECONDWORD=(SELECT SUBSTRING(@TOTALAMOUNT,2,2))
IF(@SECONDWORD!=00 )
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2))+'.'+(SELECT SUBSTRING(@TOTALAMOUNT,3,2)) + ' Arab'
ELSE
SET @AMOUNTWORD=(SELECT SUBSTRING(@TOTALAMOUNT,1,2)) + ' Arab'
END
RETURN @AMOUNTWORD
END
Output
Select dbo.AmountToWords('900000')
as Money
Output : 9 Lacs
No comments :
Post a Comment