Thursday, January 27, 2011

Converting number into words in SQL Server, Java and C#

Converting number into words in SQL Server, Java and C#


This sql function and call(fNumToWords)it wher ever u want
1.First convert digit to figures
2.it converts 10-99 digit to figures



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

2 function this will convert 10-99

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

3.The following function calls above two functions
we need to call this function to see the output.

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
-----------------------------------------------------------------------------------

This is the sql Server Function that will generate String word of the number input...

The Java Function in English Format will be...............................................................................


/**
*
* @author Laxman @link laxmangautam@gmail.com
*/
public class NumberToWord {
/**
* This Method will convert and display the Word of the Input integer Number
* For example it is use full to display the Amount in wrod of Money Value
* In English Format like ###,###,###
* @param inputNum
* @return the string in word of the input.
*/
public static String integerToWords(long inputNum)
{
int dig1, dig2, dig3, level = 0, lasttwo, threeDigits;

String retval = "";
String x = "";
String[] ones ={
"zero",
"one",
"two",
"three",
"four",
"five",
"six",
"seven",
"eight",
"nine",
"ten",
"eleven",
"twelve",
"thirteen",
"fourteen",
"fifteen",
"sixteen",
"seventeen",
"eighteen",
"nineteen"
};
String[] tens ={
"zero",
"ten",
"twenty",
"thirty",
"forty",
"fifty",
"sixty",
"seventy",
"eighty",
"ninety"
};
String[] thou ={
"",
"thousand",
"million",
"billion",
"trillion",
"quadrillion",
"quintillion"
};

boolean isNegative = false;
if (inputNum < 0)
{
isNegative = true;
inputNum *= -1;
}

if (inputNum == 0)
return ("zero");

String s = String.valueOf(inputNum);

while (s.length() > 0)
{
// Get the three rightmost characters
x = (s.length() < 3) ? s : s.substring(s.length() - 3, s.length());

// Separate the three digits
threeDigits = Integer.parseInt(x);
lasttwo = threeDigits % 100;
dig1 = threeDigits / 100;
dig2 = lasttwo / 10;
dig3 = (threeDigits % 10);

// append a "thousand" where appropriate
if (level > 0 && dig1 + dig2 + dig3 > 0)
{
retval = thou[level] + " " + retval;
retval = retval.trim();
}

// check that the last two digits is not a zero
if (lasttwo > 0)
{
if (lasttwo < 20) // if less than 20, use "ones" only
retval = ones[lasttwo] + " " + retval;
else // otherwise, use both "tens" and "ones" array
retval = tens[dig2] + " " + ones[dig3] + " " + retval;
}

// if a hundreds part is there, translate it
if (dig1 > 0)
retval = ones[dig1] + " hundred " + retval;

s = (s.length() - 3) > 0 ? s.substring(0, s.length() - 3) : "";
level++;
}

while (retval.indexOf(" ") > 0)
retval = retval.replace(" ", " ");

retval = retval.trim();

if (isNegative)
retval = "negative " + retval;

return (retval);
}

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
System.out.println("The word or 987654321 "+integerToWords(987654321));
// The out put will be
//The word or 987654321 nine hundred eighty seven million six hundred fifty four thousand three hundred twenty one

}

}

------------------------------------------------------------------------------------------------------------------------
C# Function will be...............
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
MessageBox.Show(IntegerToWords(23) , "System Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

public string IntegerToWords(long inputNum)
{
int dig1, dig2, dig3, level = 0, lasttwo, threeDigits;

string retval = "";
string x = "";
string[] ones ={
"zero",
"one",
"two",
"three",
"four",
"five",
"six",
"seven",
"eight",
"nine",
"ten",
"eleven",
"twelve",
"thirteen",
"fourteen",
"fifteen",
"sixteen",
"seventeen",
"eighteen",
"nineteen"
};
string[] tens ={
"zero",
"ten",
"twenty",
"thirty",
"forty",
"fifty",
"sixty",
"seventy",
"eighty",
"ninety"
};
string[] thou ={
"",
"thousand",
"million",
"billion",
"trillion",
"quadrillion",
"quintillion"
};

bool isNegative = false;
if (inputNum < 0)
{
isNegative = true;
inputNum *= -1;
}

if (inputNum == 0)
return ("zero");

string s = inputNum.ToString();

while (s.Length > 0)
{
// Get the three rightmost characters
x = (s.Length < 3) ? s : s.Substring(s.Length - 3, 3);

// Separate the three digits
threeDigits = int.Parse(x);
lasttwo = threeDigits % 100;
dig1 = threeDigits / 100;
dig2 = lasttwo / 10;
dig3 = (threeDigits % 10);

// append a "thousand" where appropriate
if (level > 0 && dig1 + dig2 + dig3 > 0)
{
retval = thou[level] + " " + retval;
retval = retval.Trim();
}

// check that the last two digits is not a zero
if (lasttwo > 0)
{
if (lasttwo < 20) // if less than 20, use "ones" only
retval = ones[lasttwo] + " " + retval;
else // otherwise, use both "tens" and "ones" array
retval = tens[dig2] + " " + ones[dig3] + " " + retval;
}

// if a hundreds part is there, translate it
if (dig1 > 0)
retval = ones[dig1] + " hundred " + retval;

s = (s.Length - 3) > 0 ? s.Substring(0, s.Length - 3) : "";
level++;
}

while (retval.IndexOf(" ") > 0)
retval = retval.Replace(" ", " ");

retval = retval.Trim();

if (isNegative)
retval = "negative " + retval;

return (retval);
}
}
}





Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)