Archive for September, 2016

Convert Number to Word In SQL Sever

September 20, 2016 Leave a comment

Hi all,

Try this example to convert number to word in SQL Server

Create FUNCTION [dbo].[NumberToWords]
@Amount bigint
RETURNS nvarchar(max)
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'),
insert into @Decades(Id,Name) values(20,'Twenty'),(30,'Thirty'),
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

Hope this helps

Good Luck.

Categories: SQL Server