Introduction :
HI, here I will explain you how to Convert the numbers into words in indian currency format. Here we Should Run three functions one by one. Lets Checkout the Three functions.
***Important steps to follow*** :
1) Run First function i.e., fConvertDigit.
2)Run second Function fConvertTens.
3)Run third function fNumToWords.
4)Give input to the third function and get the result.
Function 1 :
create Function dbo.fConvertDigit(@decNumber decimal)
returns varchar(6)
as
Begin
declare
@strWords varchar(6)
Select
@strWords = Case
@decNumber
When '1' then 'One'
When '2' then 'Two'
When '3' then 'Three'
When '4' then 'Four'
When '5' then 'Five'
When '6' then 'Six'
When '7' then 'Seven'
When '8' then 'Eight'
When '9' then 'Nine'
Else ''
end
return
@strWords
end
Function 2 :
create Function dbo.fConvertTens(@decNumber varchar(2))
returns varchar(30)
as
Begin
declare
@strWords varchar(30)
--Is
value between 10 and 19?
If Left(@decNumber, 1) = 1
begin
Select
@strWords = Case
@decNumber
When '10' then 'Ten'
When '11' then 'Eleven'
When '12' then 'Twelve'
When '13' then 'Thirteen'
When '14' then 'Fourteen'
When '15' then 'Fifteen'
When '16' then 'Sixteen'
When '17' then 'Seventeen'
When '18' then 'Eighteen'
When '19' then 'Nineteen'
end
end
else -- otherwise it's between 20 and 99.
begin
Select
@strWords = Case
Left(@decNumber,
1)
When '0' then ''
When '2' then 'Twenty '
When '3' then 'Thirty '
When '4' then 'Forty '
When '5' then 'Fifty '
When '6' then 'Sixty '
When '7' then 'Seventy '
When '8' then 'Eighty '
When '9' then 'Ninety '
end
Select
@strWords = @strWords +
dbo.fConvertDigit(Right(@decNumber, 1))
end
--Convert
ones place digit.
return
@strWords
end
Function 3 :
IF OBJECT_ID('fNumToWords') IS NOT NULL
DROP FUNCTION fNumToWords
go
create function dbo.fNumToWords (@decNumber decimal(12, 2))
returns varchar(300)
As
Begin
Declare
@strNumber varchar(100),
@strRupees varchar(200),
@strPaise varchar(100),
@strWords varchar(300),
@intIndex integer,
@intAndFlag integer
Select
@strNumber = Cast(@decNumber as varchar(100))
Select
@intIndex = CharIndex('.', @strNumber)
if(@decNumber>99999999.99)
BEGIN
RETURN ''
END
If
@intIndex > 0
begin
Select
@strPaise = dbo.fConvertTens(Right(@strNumber, Len(@strNumber) - @intIndex))
Select
@strNumber = SubString(@strNumber, 1, Len(@strNumber) - 3)
If Len(@strPaise) > 0 Select @strPaise =
@strPaise + ' paise'
end
Select
@strRupees = ''
Select
@intIndex=len(@strNumber)
Select
@intAndFlag=2
while(@intIndex>0)
begin
if(@intIndex=8)
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(left(@decNumber,1))+' Crore '
Select
@strNumber=substring(@strNumber,2,len(@strNumber))
Select
@intIndex=@intIndex-1
end
else if(@intIndex=7)
begin
if(substring(@strNumber,1,1)='0')
begin
if substring(@strNumber,2,1)<>'0'
begin
if (@strRupees<>NULL and substring(@strNumber,3,1)='0'
and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0'
and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0'
and
@intAndFlag=2 and
@strPaise=NULL)
begin
Select
@strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
Select
@intAndFlag=1
end
else
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
end
Select
@strNumber=substring(@strNumber,3,len(@strNumber))
Select
@intIndex=@intIndex-2
end
else
begin
Select
@strNumber=substring(@strNumber,3,len(@strNumber))
Select
@intIndex=@intIndex-2
end
end
else
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and
substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and
substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select
@strRupees=@strRupees+' and ' + dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
Select
@intAndFlag=1
end
else
begin
Select
@strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
end
Select
@strNumber=substring(@strNumber,3,len(@strNumber))
Select
@intIndex=@intIndex-2
end
end
else if(@intIndex=6)
begin
if(substring(@strNumber,2,1)<>'0' or substring(@strNumber,3,1)<>'0'
and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and
substring(@strNumber,6,1)='0' and @intAndFlag=2 and @strPaise='')
begin
if len(@strRupees) <= 0
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select
@strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
Select
@intAndFlag=2
end
else
begin
Select
@strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
Select
@intAndFlag=2
end
end
else
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select
@strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
Select
@intAndFlag=1
end
else
begin
Select
@strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
Select
@intAndFlag=1
end
end
end
else
begin
if convert(int,substring(@strNumber,1,1)) = 1
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
end
else
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
end
end
Select
@strNumber=substring(@strNumber,2,len(@strNumber))
Select
@intIndex=@intIndex-1
end
else if(@intIndex=5)
begin
if(substring(@strNumber,1,1)='0')
begin
if substring(@strNumber,2,1)<>'0'
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0'
and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select
@strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
Select
@intAndFlag=1
end
else
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
end
Select
@strNumber=substring(@strNumber,3,len(@strNumber))
Select
@intIndex=@intIndex-2
end
else
begin
Select
@strNumber=substring(@strNumber,3,len(@strNumber))
Select
@intIndex=@intIndex-2
end
end
else
begin
if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0'
and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
begin
--Select
@strRupees=@strRupees+'andjo'+dbo.fConvertTens(substring(@strNumber,1,2))+'
Thousand '
Select
@strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
Select
@intAndFlag=1
end
else
begin
Select
@strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
end
Select
@strNumber=substring(@strNumber,3,len(@strNumber))
Select
@intIndex=@intIndex-2
end
end
else if(@intIndex=4)
begin
if ( (substring(@strNumber,3,1)<>'0' or substring(@strNumber,4,1)<>'0') and
substring(@strNumber,2,1)='0' and @intAndFlag=2 and @strPaise='')
begin
Select
@strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
Select
@intAndFlag=1
end
else
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
end
Select
@strNumber=substring(@strNumber,2,len(@strNumber))
Select
@intIndex=@intIndex-1
end
else if(@intIndex=3)
begin
if substring(@strNumber,1,1)<>'0'
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Hundred '
Select
@strNumber=substring(@strNumber,2,len(@strNumber))
if( (substring(@strNumber,1,1)<>'0' or substring(@strNumber,2,1)<>'0') and
@intAndFlag=2 )
begin
Select
@strRupees=@strRupees+' and '
Select
@intAndFlag=1
end
Select
@intIndex=@intIndex-1
end
else
begin
Select
@strNumber=substring(@strNumber,2,len(@strNumber))
Select
@intIndex=@intIndex-1
end
end
else if(@intIndex=2)
begin
if substring(@strNumber,1,1)<>'0'
begin
Select
@strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))
Select
@intIndex=@intIndex-2
end
else
begin
Select
@intIndex=@intIndex-1
end
end
else if(@intIndex=1)
begin
if(@strNumber<>'0')
begin
Select
@strRupees=@strRupees+dbo.fConvertDigit(@strNumber)
end
Select
@intIndex=@intIndex-1
end
continue
end
if len(@strRupees)>0 Select
@strRupees=@strRupees+
' rupees '
IF(len(@strPaise)<>0)
BEGIN
if len(@strRupees)>0 Select
@strRupees=@strRupees +
' and '
END
Select
@strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
select
@strWords = @strWords +
' only'
Return
@strWords
End
OUTPUT :
select dbo.fNumToWords(1000)
result: One Thousand rupees only