mssql convert from number to words edit button Edit

Murugan Andezuthu Dharmaratnam | calendar 10 September 2020 | 2358

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