SQL function for converting Numbers to words in Indian Currency as Rupees format.

 

 

 

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