Number to Text in SQL

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
BEGINDeclare @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)))
 Select @SNumToWords = ''
 If Len(LTrim(RTrim(convert(varchar,@Number)))) > 4
 
Begin
Select @SLacs = Substring(@StrNumber,1,2)
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
              Select @SNumToWords = @SNumToWords + Space(1) + @Wwords  
      End

If @IUnt > 0
Begin 
          Select @Wwords = ''
          Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
          Select @SNumToWords = @SNumToWords + Space(1) + @Wwords
End
Select @SNumToWords = @SNumToWords + ' Lacs'
End

Select @SThou = Substring(@StrNumber,3,2)
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
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords
End
If @IUnt > 0
Begin 
Select @Wwords =  ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords
End
Select @SNumToWords = @SNumToWords + ' Thousand '
End
Select @SHun = Substring(@StrNumber,5,1)
Select @IHun = Convert(int,@SHun)

If @IHun > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IHun
Select @SNumToWords = @SNumToWords + @Wwords + ' Hundred'
End

Select @STenUnt = Substring(@StrNumber,6,2)
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
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords

End

If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
Select @SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
End
End

Select @SNumToWords = 'Rupees' + @SNumToWords + Space(1) --Only/-

End
Else
Begin
Select @SLacs = Substring(LTrim(RTrim(convert(varchar,@Number))),1,1)
Select @ILacs = Convert(int,@SLacs)
If @ILacs > 0 and @ILacs <> 1
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
Select @SNumToWords = 'Rupees' + @SNumToWords + Space(1) + @Wwords + Space(1)
End
Else
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
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

Select @SNumToWords = @SNumToWords + Space(1) + @Wwords
End
If @IUnt > 0
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @IUnt
Select @SNumToWords = @SNumToWords + Space(1) + @Wword
End
Select @SNumToWords = @SNumToWords + Space(1) + 'Paise'
End
End
Return LTrim(RTrim(@SNumToWords))

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

 

you can use to convert any integer value to text in any  table