s
Murugan Andezuthu Dharmaratnam | 10 September 2020 | 2576

this ms sql function will convert any numeric value to words eg. 65000 to Sixty Five Thousand. I found this code while I writing an Invoice application where I had to display the amount in words.

## MS SQL Function To Convert from Numeric To Words

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
GO