Skip to Content
0
Former Member
Feb 03, 2010 at 04:38 AM

Amount in words in indian format

31 Views

Dear All,

I have refer this link to get amount in words in indian format.but it is showing me error.what i copied in sql is -

*if exists (select * from dbo.sysobjects where id = object_id(N'dbo.AmountToWords') and xtype in (N'FN', N'IF', N'TF')) drop function dbo.AmountToWords GO if exists (select * from dbo.sysobjects where id = object_id(N'dbo.GetTextValue') and xtype in (N'FN', N'IF', N'TF')) drop function dbo.GetTextValue GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CREATE function AmountToWords ( @InNumber Numeric(18,2) ) --Returns the number as words. returns VARCHAR(2000) as BEGIN --SEt NoCount ON Declare @Num Varchar(20) Declare @Dec Varchar(3) Declare @Return Varchar(2000) Set @Dec = SubString(Convert(Varchar(20),@Innumber),Len(Convert(Varchar(20),@Innumber))-2,3) Set @Num = SubString(Convert(Varchar(20),@Innumber),1, Len(Convert(Varchar(20),@Innumber))-3) Declare @Hundred Char(8) Declare @HundredAnd Char(12) Declare @Thousand Char(9) Declare @Lakh Char(5) Declare @Lakhs Char(6) Declare @Crore Char(6) Declare @Crores Char(7) Set @Hundred = 'Hundred ' Set @Thousand = 'Thousand ' Set @Lakh = 'Lakh ' Set @Lakhs = 'Lakhs ' Set @Crore = 'Crore ' Set @Crores = 'Crores ' Set @HundredAnd = 'Hundred and ' if Len(@Num) = 1 -- One Begin Set @Return = dbo.GetTextValue(@Num) End Else if Len(@Num) = 2 -- Ten Begin Set @Return = dbo.GetTextValue(@Num) End Else if Len(@Num) = 3 -- Hundred Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Hundred IF SubString(@num,2,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) End Else if Len(@Num) = 4 -- thousand Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Thousand If SubString(@Num,2,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,1)) + @Hundred IF SubString(@num,3,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) End Else if Len(@Num) = 5 -- Ten Thousand Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Thousand If SubString(@Num,3,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,1)) + @Hundred IF SubString(@num,4,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) End Else if Len(@Num) = 6 -- Lakh Begin If SubString(@Num,1,1) = '1' Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakh Else Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakhs If SubString(@Num,2,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Thousand If SubString(@Num,4,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,1)) + @Hundred IF SubString(@num,5,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) End Else if Len(@Num) = 7 -- Ten Lakhs Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Lakhs If SubString(@Num,3,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Thousand If SubString(@Num,6,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,1)) + @Hundred IF SubString(@num,6,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) End Else if Len(@Num) = 8 -- Crore Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Crore If SubString(@Num,2,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Lakhs If SubString(@Num,4,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Thousand If SubString(@Num,6,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,1)) + @Hundred IF SubString(@num,7,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) End Else if Len(@Num) = 9 -- Ten Crore Begin Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Crores If SubString(@Num,3,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Lakhs If SubString(@Num,5,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Thousand If SubString(@Num,7,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,1)) + @Hundred IF SubString(@num,8,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) End Else if Len(@Num) = 10 -- Hundred Crore Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Hundred IF Substring(@Num,2,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) Set @Return = @Return + @Crores If SubString(@Num,4,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Lakhs If SubString(@Num,6,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Thousand If SubString(@Num,8,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,1)) + @Hundred IF SubString(@num,9,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) End Else if Len(@Num) = 11 -- Thousand Crore Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Thousand IF SubString(@Num,2,1) '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,1)) + @Hundred IF Substring(@Num,3,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) Set @Return = @Return + @Crores If SubString(@Num,5,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Lakhs If SubString(@Num,7,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Thousand If SubString(@Num,9,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,1)) + @Hundred IF SubString(@num,10,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) End Else if Len(@Num) = 12 -- Ten thousand Crore Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Thousand IF SubString(@Num,3,1) '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,1)) + @Hundred IF Substring(@Num,4,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) Set @Return = @Return + @Crores If SubString(@Num,6,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Lakhs If SubString(@Num,8,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Thousand If SubString(@Num,10,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,1)) + @Hundred IF SubString(@num,11,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,2)) End Else if Len(@Num) = 13 -- Lakh Crore Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Lakh If Substring(@Num,2,2) '00' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,2)) + @Thousand IF SubString(@Num,4,1) '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,4,1)) + @Hundred IF Substring(@Num,5,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) Set @Return = @Return + @Crores If SubString(@Num,7,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Lakhs If SubString(@Num,9,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) + @Thousand If SubString(@Num,11,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,1)) + @Hundred IF SubString(@num,12,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,2)) End Else if Len(@Num) = 14 -- Ten Lakh Crore Begin Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Lakhs If Substring(@Num,3,2) '00' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,2)) + @Thousand IF SubString(@Num,5,1) '0' Set @Return = @Return + dbo.GetTextValue(Substring(@Num,5,1)) + @Hundred IF Substring(@Num,6,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) Set @Return = @Return + @Crores If SubString(@Num,8,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Lakhs If SubString(@Num,10,2) '00' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) + @Thousand If SubString(@Num,12,1) '0' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,1)) + @Hundred IF SubString(@num,13,2) '00' Set @Return = @Return + 'And ' Set @Return = @Return + dbo.GetTextValue(SubString(@Num,13,2)) End If @Dec '.00' Set @Return = @Return + 'Rupees And ' + dbo.GetTextValue(SubString(@Dec,2,2)) + 'Paise' ELSE Set @Return =@Return +'Rupees' Return @return End GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE Function dbo.GetTextValue ( @dblNumber Numeric ) Returns Varchar(1000) As Begin Declare @StrWord Varchar(400) SEt @strWord = Case @dblNumber When 1 Then 'One ' When 2 Then 'Two ' When 3 Then 'Three ' When 4 Then 'Four ' When 5 Then 'Five ' When 6 Then 'Six ' When 7 Then 'Seven ' When 8 Then 'Eight ' When 9 Then 'Nine ' When 10 Then 'Ten ' When 11 Then 'Eleven ' When 12 Then 'Twelve ' When 13 Then 'Thirteen ' When 14 Then 'Fourteen ' When 15 Then 'Fifteen ' When 16 Then 'Sixteen ' When 17 Then 'Seventeen ' When 18 Then 'Eighteen ' When 19 Then 'Nineteen ' When 20 Then 'Twenty ' When 21 Then 'Twenty One ' When 22 Then 'Twenty Two ' When 23 Then 'Twenty Three ' When 24 Then 'Twenty Four ' When 25 Then 'Twenty Five ' When 26 Then 'Twenty Six ' When 27 Then 'Twenty Seven ' When 28 Then 'Twenty Eight ' When 29 Then 'Twenty Nine ' When 30 Then 'Thirty ' When 31 Then 'Thirty One ' When 32 Then 'Thirty Two ' When 33 Then 'Thirty Three ' When 34 Then 'Thirty Four ' When 35 Then 'Thirty Five ' When 36 Then 'Thirty Six ' When 37 Then 'Thirty Seven ' When 38 Then 'Thirty Eight ' When 39 Then 'Thirty Nine ' When 40 Then 'Fourty ' When 41 Then 'Fourty One ' When 42 Then 'Fourty Two ' When 43 Then 'Fourty Three ' When 44 Then 'Fourty Four ' When 45 Then 'Fourty Five ' When 46 Then 'Fourty Six ' When 47 Then 'Fourty Seven ' When 48 Then 'Fourty Eight ' When 49 Then 'Fourty Nine ' When 50 Then 'Fifty ' When 51 Then 'Fifty One ' When 52 Then 'Fifty Two ' When 53 Then 'Fifty Three ' When 54 Then 'Fifty Four ' When 55 Then 'Fifty Five ' When 56 Then 'Fifty Six ' When 57 Then 'Fifty Seven ' When 58 Then 'Fifty Eight ' When 59 Then 'Fifty Nine ' When 60 Then 'Sixty ' When 61 Then 'Sixty One ' When 62 Then 'Sixty Two ' When 63 Then 'Sixty Three ' When 64 Then 'Sixty Four ' When 65 Then 'Sixty Five ' When 66 Then 'Sixty Six ' When 67 Then 'Sixty Seven ' When 68 Then 'Sixty Eight ' When 69 Then 'Sixty Nine ' When 70 Then 'Seventy ' When 71 Then 'Seventy One ' When 72 Then 'Seventy Two ' When 73 Then 'Seventy Three ' When 74 Then 'Seventy Four ' When 75 Then 'Seventy Five ' When 76 Then 'Seventy Six ' When 77 Then 'Seventy Seven ' When 78 Then 'Seventy Eight ' When 79 Then 'Seventy Nine ' When 80 Then 'Eighty ' When 81 Then 'Eighty One ' When 82 Then 'Eighty Two ' When 83 Then 'Eighty Three ' When 84 Then 'Eighty Four ' When 85 Then 'Eighty Five ' When 86 Then 'Eighty Six ' When 87 Then 'Eighty Seven ' When 88 Then 'Eighty Eight ' When 89 Then 'Eighty Nine ' When 90 Then 'Ninety ' When 91 Then 'Ninety One ' When 92 Then 'Ninety Two ' When 93 Then 'Ninety Three ' When 94 Then 'Ninety Four ' When 95 Then 'Ninety Five ' When 96 Then 'Ninety Six ' When 97 Then 'Ninety Seven ' When 98 Then 'Ninety Eight ' When 99 Then 'Ninety Nine ' When 100 Then 'One hundred ' else ' ' End Return @strWord End GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO*

but it showed me error.Let me know where i am getting wrong.

I also created 1 UDF on the document where you want the amount in words as per indian format. run the query generateor,paste the below query... and save it. declare @Doc_total numeric (19,6) set @Doc_total=$[OINV.DocTotal] select dbo.AmountToWords (@Doc_total) now apply formated search on the UDF you created(For e.g. i created on SALes Invoice) refresh regularly on doctotal. now choose that UDF on pld. Hope this will help u. Regards, Neetu

Regards-

Mona.