12,455,701 members (70,341 online)
Tip/Trick
alternative version

14.7K views
17 bookmarked
Posted

# Numbers to Words in C#, SQL Server and Crystal Reports

, 23 Nov 2015 CPOL
 Rate this:
This tip will help convert numbers to words in C#, SQL Server, and Crystal Reports.

## Introduction

In this tip, I am trying to give a solution for converting numbers to words. It is very essential for software developers. For financial solutions, we may need to translate into words, e.g., in a receipt, voucher, cheque print, etc. We can do it in C#, SQL Server, Crystal Reports. In this regard, I gave a solution in C#, SQL Server, Crystal Reports.

## Using the Code

In C#, I used a recursive function in which two `string` arrays are used for `1` to `99`, when the number will be more than 99, the function will call itself recursively.

```public static string NumToWordBD(Int64 Num)
{
string[] Below20 = { "", "One ",
"Two ", "Three ", "Four ",
"Five ", "Six " , "Seven ",
"Eight ", "Nine ", "Ten ", "Eleven ",
"Twelve " , "Thirteen ", "Fourteen ","Fifteen ",
"Sixteen " , "Seventeen ","Eighteen " , "Nineteen " };
string[] Below100 = { "", "", "Twenty ", "Thirty ",
"Forty ", "Fifty ", "Sixty ",
"Seventy ", "Eighty ", "Ninety " };
string InWords="";
if(Num >= 1 && Num < 20)
InWords += Below20[Num];
if(Num >=20 && Num <= 99)
InWords += Below100[Num / 10] + Below20[Num % 10];
if (Num >= 100 && Num <= 999)
InWords += NumToWordBD(Num / 100) + " Hundred " + NumToWordBD(Num % 100);
if (Num >= 1000 && Num <= 99999)
InWords += NumToWordBD(Num / 1000) + " Thousand " + NumToWordBD(Num % 1000);
if (Num >= 100000 && Num <= 9999999)
InWords += NumToWordBD(Num / 100000) + " Lac " + NumToWordBD(Num % 100000);
if (Num >= 10000000)
InWords += NumToWordBD(Num / 10000000) + " Crore " + NumToWordBD(Num % 10000000);
return InWords;
}```

In SQL Server: In SQL Server, I used a recursive functions as in C#. Here, I used two temp Tables. When the number is less than 100, it will return words from temp table. Here Id represents the number. It will call itself when the number is greater than 99.

```create FUNCTION [dbo].[NumberToWords]
(
@Amount bigint
)
RETURNS nvarchar(max)
AS
BEGIN
declare @Ones table (Id int, Name nvarchar(50))
declare @Decades table (Id int, Name nvarchar(50))
insert into  @Ones(Id,Name) values(0,''),(1,'One'),_
(2,'Two'),(3,'Three'),(4,'Four'),(5,'Five'),_
(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine'),_
(10,'Ten'),(11,'Eleven'),(12,'Twelve'),(13,'Thirteen'),_
(14,'Forteen'),(15,'Fifteen'),(16,'Sixteen'),_
(17,'Seventeen'),(18,'Eighteen'),(19,'Nineteen')
(40,'Forty'),(50,'Fifty'),(60,'Sixty'),_
(70,'Seventy'),(80,'Eighty'),(90,'Ninety')
declare @str nvarchar(max)
set @str=''

if(@Amount >= 1 AND @Amount <20)
set @str=@str+ (select Name from @Ones where Id=@Amount)

if(@Amount >= 20 AND @Amount <=99)
set @str=@str+ (select Name From @Decades where Id= _
(@Amount- @Amount%10))+' ' +(select Name From @Ones where Id=(@Amount%10)) +' '

if(@Amount >= 100 AND @Amount <=999)
set @str=@str+ dbo.NumberToWords(@Amount/100) +' _
Hundred '+dbo.NumberToWords(@Amount%100)

if(@Amount >= 1000 AND @Amount <=99999)
set @str=@str+ dbo.NumberToWords(@Amount/1000) +' _
Thousand '+dbo.NumberToWords(@Amount%1000)

if(@Amount >= 100000 AND @Amount <=9999999)
set @str=@str+ dbo.NumberToWords(@Amount/100000) +' _
Lac '+dbo.NumberToWords(@Amount%100000)

if(@Amount >= 10000000 )
set @str=@str+ dbo.NumberToWords(@Amount/10000000) +' _
Crore '+dbo.NumberToWords(@Amount%10000000)

return @str
END```

In Crystal Reports: Create a formula field, copy the code, and replace `@TotalNumber` with your number field to be converted to text.

```numbervar RmVal:=0;
numbervar Amt:=0;
numbervar pAmt:=0;
stringvar InWords :="Taka ";
Amt := {@TotalNumber} ;

if Amt > 10000000 then RmVal := truncate(Amt/10000000);
if Amt = 10000000 then RmVal := 1;
if RmVal = 1 then
InWords := InWords + " " + ProperCase (towords(RmVal,0)) + " Crore"
else
if RmVal > 1 then InWords := InWords + " " + _
ProperCase (towords(RmVal,0)) + " Crore";
Amt := Amt - Rmval * 10000000;
if Amt > 100000 then RmVal := truncate(Amt/100000);
if Amt = 100000 then RmVal := 1;
if RmVal = 1 then
InWords := InWords + " " + ProperCase (towords(RmVal,0)) + " Lac"
Else
If RmVal > 1 then InWords := InWords + " " + _
ProperCase (ToWords(RmVal,0)) + " Lac";
Amt := Amt - Rmval * 100000;
if Amt > 0 then InWords := InWords + " " + _
ProperCase (towords(truncate(Amt),0));
pAmt := (Amt - truncate(Amt)) * 100;
if pAmt > 0 then
InWords := InWords + " and " + _
ProperCase (towords(pAmt,0)) + " paise only"
else
InWords := InWords + " only";```

## Points of Interest

Actually, I realized the need for this tip when facing some problems while developing a financial solution.

## Share

ROFIQUL ISLAM
M.Sc, MCP

I am a C, C++ ,VB6, and C# software developer in Bangladesh. I actually work for LEADS Corporation Limited with C#/ASP.Net and Microsoft SQL Server database.I have good experience in software development life cycle (analysis, requirements, design, coding, test and deploy).
3+ years in .NET programming (especially C#);

## You may also be interested in...

 Pro Pro

 First Prev Next
 My vote of 5 JCahyaatnttearjee25-Nov-15 19:17 JCahyaatnttearjee 25-Nov-15 19:17
 good post Member 1023758225-Nov-15 17:24 Member 10237582 25-Nov-15 17:24
 nice post Member 1216505824-Nov-15 17:53 Member 12165058 24-Nov-15 17:53
 It didn't seem to work too good.... Steve Wellens13-Oct-15 4:12 Steve Wellens 13-Oct-15 4:12