hi,
I have write a small programm in sql server for number convet into word. But if I type 1234567 then it is showing convert in word. But if I type 234567 then it is not showing. Kinldly help me this coding with changes, So I can improve my knowledge in Sql Server.
coding :
if object_id('dbo.tblAmountWords') is not null
drop table dbo.tblAmountWords
go
create table dbo.tblAmountWords (
AmountValue numeric(18,0) not null,
DollarWord varchar(50) not null,
CentWord char(6) null)
go
insert dbo.tblAmountWords
select 0, 'Zero' , '00/100' union
select 1, 'One' , '01/100' union
select 2, 'Two' , '02/100' union
select 3, 'Three' , '03/100' union
select 4, 'Four' , '04/100' union
select 5, 'Five' , '05/100' union
select 6, 'Six' , '06/100' union
select 7, 'Seven' , '07/100' union
select 8, 'Eight' , '08/100' union
select 9, 'Nine' , '09/100' union
select 10, 'Ten' , '10/100' union
select 11, 'Eleven' , '11/100' union
select 12, 'Twelve' , '12/100' union
select 13, 'Thirteen' , '13/100' union
select 14, 'Fourteen' , '14/100' union
select 15, 'Fifteen' , '15/100' union
select 16, 'Sixteen' , '16/100' union
select 17, 'Seventeen' , '17/100' union
select 18, 'Eighteen' , '18/100' union
select 19, 'Nineteen' , '19/100' union
select 20, 'Twenty' , '20/100' union
select 21, 'Twenty One' , '21/100' union
select 22, 'Twenty Two' , '22/100' union
select 23, 'Twenty Three' , '23/100' union
select 24, 'Twenty Four' , '24/100' union
select 25, 'Twenty Five' , '25/100' union
select 26, 'Twenty Six' , '26/100' union
select 27, 'Twenty Seven' , '27/100' union
select 28, 'Twenty Eight' , '28/100' union
select 29, 'Twenty Nine' , '29/100' union
select 30, 'Thirty' , '30/100' union
select 31, 'Thirty One' , '31/100' union
select 32, 'Thirty Two' , '32/100' union
select 33, 'Thirty Three' , '33/100' union
select 34, 'Thirty Four' , '34/100' union
select 35, 'Thirty Five' , '35/100' union
select 36, 'Thirty Six' , '36/100' union
select 37, 'Thirty Seven' , '37/100' union
select 38, 'Thirty Eight' , '38/100' union
select 39, 'Thirty Nine' , '39/100' union
select 40, 'Forty' , '40/100' union
select 41, 'Forty One' , '41/100' union
select 42, 'Forty Two' , '42/100' union
select 43, 'Forty Three' , '43/100' union
select 44, 'Forty Four' , '44/100' union
select 45, 'Forty Five' , '45/100' union
select 46, 'Forty Six' , '46/100' union
select 47, 'Forty Seven' , '47/100' union
select 48, 'Forty Eight' , '48/100' union
select 49, 'Forty Nine' , '49/100' union
select 50, 'Fifty' , '50/100' union
select 51, 'Fifty One' , '51/100' union
select 52, 'Fifty Two' , '52/100' union
select 53, 'Fifty Three' , '53/100' union
select 54, 'Fifty Four' , '54/100' union
select 55, 'Fifty Five' , '55/100' union
select 56, 'Fifty Six' , '56/100' union
select 57, 'Fifty Seven' , '57/100' union
select 58, 'Fifty Eight' , '58/100' union
select 59, 'Fifty Nine' , '59/100' union
select 60, 'Sixty' , '60/100' union
select 61, 'Sixty One' , '61/100' union
select 62, 'Sixty Two' , '62/100' union
select 63, 'Sixty Three' , '63/100' union
select 64, 'Sixty Four' , '64/100' union
select 65, 'Sixty Five' , '65/100' union
select 66, 'Sixty Six' , '66/100' union
select 67, 'Sixty Seven' , '67/100' union
select 68, 'Sixty Eight' , '68/100' union
select 69, 'Sixty Nine' , '69/100' union
select 70, 'Seventy' , '70/100' union
select 71, 'Seventy One' , '71/100' union
select 72, 'Seventy Two' , '72/100' union
select 73, 'Seventy Three' , '73/100' union
select 74, 'Seventy Four' , '74/100' union
select 75, 'Seventy Five' , '75/100' union
select 76, 'Seventy Six' , '76/100' union
select 77, 'Seventy Seven' , '77/100' union
select 78, 'Seventy Eight' , '78/100' union
select 79, 'Seventy Nine' , '79/100' union
select 80, 'Eighty' , '80/100' union
select 81, 'Eighty One' , '81/100' union
select 82, 'Eighty Two' , '82/100' union
select 83, 'Eighty Three' , '83/100' union
select 84, 'Eighty Four' , '84/100' union
select 85, 'Eighty Five' , '85/100' union
select 86, 'Eighty Six' , '86/100' union
select 87, 'Eighty Seven' , '87/100' union
select 88, 'Eighty Eight' , '88/100' union
select 89, 'Eighty Nine' , '89/100' union
select 90, 'Ninety' , '90/100' union
select 91, 'Ninety One' , '91/100' union
select 92, 'Ninety Two' , '92/100' union
select 93, 'Ninety Three' , '93/100' union
select 94, 'Ninety Four' , '94/100' union
select 95, 'Ninety Five' , '95/100' union
select 96, 'Ninety Six' , '96/100' union
select 97, 'Ninety Seven' , '97/100' union
select 98, 'Ninety Eight' , '98/100' union
select 99, 'Ninety Nine' , '99/100' union
select 100, 'Hundred' , NULL union
select 1000, 'Thousand' , NULL union
select 1000000, 'Million' , NULL union
select 1000000000, 'Billion' , NULL
go
Declare @amt varchar(10)
Declare @amt12 varchar(50)
Declare @lenamt int
Declare @LackWord varchar(50)
Declare @ThousanWord varchar(50)
Declare @HundredWord varchar(50)
Declare @OnlyWord varchar(50)
Declare @RupeesWord varchar(800)
set @amt = '1234567'
--set @amt = 1000067
--set @amt12 = substring(CONVERT(varchar, @amt),3,5)
--select @amt12
--select @amt = 1234567/100000
--if @amt >100000 and len(@amt) > 6 or len(@amt) <= 7
if len(@amt) > 6 and len(@amt) <= 7
begin
set @lenamt = (@amt/100000)
select @lenamt
select @LackWord = DollarWord from dbo.tblAmountWords where AmountValue =@lenamt
set @amt12 = substring(CONVERT(varchar, @amt),3,5)
-- set @LackWord + ' Lacks'
end
--select @LackWord + ' Lacks'
if len(@amt12) > 4 or len(@amt12) <= 5
begin
set @lenamt = CONVERT(int, @amt12)/1000
select @ThousanWord = DollarWord from dbo.tblAmountWords where AmountValue =@lenamt
set @amt12 = substring(CONVERT(varchar, @amt),5,3)
-- set @ThousanWord + ' Thousand'
end
--select @ThousanWord + ' Thousand'
if len(@amt12) > 2 or len(@amt12) <= 3
begin
set @lenamt = CONVERT(int, @amt12)/100
select @HundredWord = DollarWord from dbo.tblAmountWords where AmountValue =@lenamt
set @amt12 = substring(CONVERT(varchar, @amt),6,2)
-- set @HundredWord + ' Hundred'
end
--select @OnlyWord + ' Hundred'
if len(@amt12) > 0 or len(@amt12) <= 2
begin
set @lenamt = CONVERT(int, @amt12)
select @OnlyWord = DollarWord from dbo.tblAmountWords where AmountValue =@lenamt
-- set @OnlyWord + ' Only'
end
--select @OnlyWord + ' Only'
set @RupeesWord = @LackWord + ' Lacks ' + @ThousanWord + ' Thousand ' + @HundredWord + ' Hundred ' + @OnlyWord + ' Only'
select @RupeesWord
|