Step 1 :--
Run this script
CREATE TABLE [dbo].[M_Words](
[Code] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
[WNumber] [int] NOT NULL,
[Wwords] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[M_Words] ON
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(1 AS Numeric(9, 0)), 0, N'Zero')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(2 AS Numeric(9, 0)), 1, N'One')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(3 AS Numeric(9, 0)), 2, N'Two')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(4 AS Numeric(9, 0)), 3, N'Three')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(5 AS Numeric(9, 0)), 4, N'Four')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(6 AS Numeric(9, 0)), 5, N'Five')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(7 AS Numeric(9, 0)), 6, N'Six')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(8 AS Numeric(9, 0)), 7, N'Seven')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(9 AS Numeric(9, 0)), 8, N'Eight')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(10 AS Numeric(9, 0)), 9, N'Nine')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(11 AS Numeric(9, 0)), 10, N'Ten')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(12 AS Numeric(9, 0)), 11, N'Eleven')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(13 AS Numeric(9, 0)), 12, N'Tweleve')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(14 AS Numeric(9, 0)), 13, N'Thirteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(15 AS Numeric(9, 0)), 14, N'Fourteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(16 AS Numeric(9, 0)), 15, N'Fifteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(17 AS Numeric(9, 0)), 16, N'Sixteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(18 AS Numeric(9, 0)), 17, N'Seventeen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(19 AS Numeric(9, 0)), 18, N'Eighteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(20 AS Numeric(9, 0)), 19, N'Ninteen')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(21 AS Numeric(9, 0)), 20, N'Twenty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(22 AS Numeric(9, 0)), 30, N'Thirty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(23 AS Numeric(9, 0)), 40, N'Fourty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(24 AS Numeric(9, 0)), 50, N'Fifty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(25 AS Numeric(9, 0)), 60, N'Sixty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(26 AS Numeric(9, 0)), 70, N'Seventy')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(27 AS Numeric(9, 0)), 80, N'Eighty')
INSERT [dbo].[M_Words] ([Code], [WNumber], [Wwords]) VALUES (CAST(28 AS Numeric(9, 0)), 90, N'Ninty')
SET IDENTITY_INSERT [dbo].[M_Words] OFF
Step 2
Make This Function
CREATE FUNCTION dbo.[Ravi_NumToWords](@Number Numeric(18,2),@CPaise Char(1))
RETURNS varchar(100) AS
BEGIN
Declare @StrNumber varchar(10), @SLacs char(2), @SThou char(2), @SHun char(2)
Declare @STenUnt char(2), @STen char(2), @SUnt char(2), @SDecimal char(2)
Declare @ILacs Int, @IThou Int, @IHun Int, @ITenUnt Int, @ITen Int, @IUnt Int, @IDecimal Int
Declare @SNumToWords varchar(100), @Wwords varchar(10)
Select @StrNumber = Replicate('0',10-Len(LTrim(RTrim(convert(varchar,@Number))))) + LTrim(RTrim(Convert(varchar,@Number)))
--Print @StrNumber
--Print Len(@StrNumber)
Select @SNumToWords = ''
--Print Len(LTrim(RTrim(convert(varchar,@Number))))
If Len(LTrim(RTrim(convert(varchar,@Number)))) > 4
Begin
--Print Len(@StrNumber)
Select @SLacs = Substring(@StrNumber,1,2)
--Print @SLacs
Select @ILacs = Convert(int,@SLacs)
If @ILacs > 0
Begin
Select @STen = Substring(@StrNumber,1,1)
Select @SUnt = Substring(@StrNumber,2,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,1,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
--Select @Wwords = ''
--Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + ' Lakh'
End
Select @SThou = Substring(@StrNumber,3,2)
--Print @SThou
Select @IThou = Convert(int,@SThou)
If @IThou > 0
Begin
Select @STen = Substring(@StrNumber,3,1)
Select @SUnt = Substring(@StrNumber,4,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,3,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
--Select @Wwords = ''
--Select @Wwords = Wwords From M_Words Where WNumber = @IThou
--Print @Wwords
--Select @SNumToWords = @SNumToWords + @Wwords + ' Thousand '
Select @SNumToWords = @SNumToWords + ' Thousand '
End
Select @SHun = Substring(@StrNumber,5,1)
--Print @SHun
Select @IHun = Convert(int,@SHun)
If @IHun > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IHun
--Print @Wwords
Select @SNumToWords = @SNumToWords + @Wwords + ' Hundred'
End
Select @STenUnt = Substring(@StrNumber,6,2)
---Print @STenUnt
Select @ITenUnt = Convert(int,@STenUnt)
If @ITenUnt > 0
Begin
Select @STen = Substring(@StrNumber,6,1)
Select @SUnt = Substring(@StrNumber,7,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,6,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
End
Select @SNumToWords = @SNumToWords + Space(1) + 'Rupees Only/-' --Only/-
End
Else
Begin
--Print Len(@StrNumber)
--Print LTrim(RTrim(convert(varchar,@Number)))
Select @SLacs = Substring(LTrim(RTrim(convert(varchar,@Number))),1,1)
--Print @SLacs
Select @ILacs = Convert(int,@SLacs)
If @ILacs > 0 and @ILacs <> 1
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
End
Else
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Rupee'
End
End
If @CPaise = 'Y'
Begin
Select @SDecimal = Substring(@StrNumber,9,2)
Select @IDecimal = Convert(int,@SDecimal)
If @IDecimal > 0
Begin
Select @SNumToWords = @SNumToWords + ' and'
Select @STen = Substring(@SDecimal,1,1)
Select @SUnt = Substring(@SDecimal,2,1)
if Convert(int,@STen) = 1
Begin
Select @ITen = Convert(int,Substring(@StrNumber,9,2))
Select @IUnt = 0
End
Else
Begin
Select @ITen = Convert(int,@STen)*10
Select @IUnt = Convert(int,@SUnt)
End
If @ITen > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ITen
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Tens'
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
--Print @Wwords
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
Select @SNumToWords = @SNumToWords + Space(1) + 'Paise'
End
End
--Print LTrim(RTrim(@SNumToWords))
Return LTrim(RTrim(@SNumToWords))
End
Step 3
Use Like This
select dbo.[Ravi_NumToWords] (15264,'y')
Note :- This Function's max limit is 1 to 9999999
0 comments:
Post a Comment