you can use to convert any integer value to text in any table
Example: Print dbo.fnNumToWords(123456,0)
----------------------Create Function and replace dbo with your server database owner object------------------------------------
CREATE FUNCTION fnNumToWords(@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 + ' Lacs'
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 = 'Rupees' + @SNumToWords + Space(1) --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 = 'Rupees' + @SNumToWords + Space(1) + @Wwords + Space(1)
End
Else
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords ='Rupee' + @SNumToWords + @Wwords + Space(1)
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
Return LTrim(RTrim(@SNumToWords))
End
-----------------------------------------------------
Then Create A table
CREATE TABLE [dbo].[M_Words](
[Code] [int] IDENTITY(1,1) NOT NULL,
[WNumber] [int] NULL,
[Wwords] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[M_Words] ADD DEFAULT ((0)) FOR [WNumber]
GO
ALTER TABLE [dbo].[M_Words] ADD DEFAULT (' ') FOR [Wwords]
GO
---------------------------------------------------End---------------------
Insert data into M_Words
Code WNumber Wwords
1 0 Zero
2 1 One
3 2 Two
4 3 Three
5 4 Four
6 5 Five
7 6 Six
8 7 Seven
9 8 Eight
10 9 Nine
11 10 Ten
12 11 Eleven
13 12 Twelve
14 13 Thirteen
15 14 Fourteen
16 15 Fifteen
17 16 Sixteen
18 17 Seventeen
19 18 Eighteen
20 19 Ninteen
21 20 Twenty
22 30 Thirty
23 40 Fourty
24 50 Fifty
25 60 Sixty
26 70 Seventy
27 80 Eighty
28 90 Ninty
---------------------------------------------------End---------------------
declare @r int
set @r=1234555
Select dbo.fnNumToWords(@r,0)as text
Out Put: Rupees Twelve Lacs Thirty Four Thousand Five Hundred Fifty Five
