Simple Function For Number To Word

,

This Simple Function Let u get the Word Format From Numeric Value. Its just like NumericToWord Function in some prog. Languages.

Just Copy and Paste it in SQL Query Analyzer and build it.

Your Views are always welcomed at mayursid@yahoo.co.in

Create Function No2Word ( @StrNo Varchar(100))
Returns Varchar(100)
As
Begin
Declare @StrRet as Varchar(100)
Declare @Len as Int

Select @Len = Len(@StrNo)

If @Len = 1 
Begin
Select@StrRet = Case @StrNo 
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'
Else ''
End
End
If @Len = 2
Begin
Select@StrRet = Case 
When @StrNo = '10' Then IsNull(@StrRet,'') + 'Ten'
When @StrNo = '11' Then IsNull(@StrRet,'') + 'Eleven'
When @StrNo = '12' Then IsNull(@StrRet,'') + 'Twelve'
When @StrNo = '13' Then IsNull(@StrRet,'') + 'Thirteen'
When @StrNo = '14' Then IsNull(@StrRet,'') + 'Fourteen'
When @StrNo = '15' Then IsNull(@StrRet,'') + 'Fifteen'
When @StrNo = '16' Then IsNull(@StrRet,'') + 'Sixteen'
When @StrNo = '17' Then IsNull(@StrRet,'') + 'Seventeen'
When @StrNo = '18' Then IsNull(@StrRet,'') + 'Eighteen'
When @StrNo = '19' Then IsNull(@StrRet,'') + 'Ninteen'
Else IsNull(@StrRet,'')
End

Select@StrRet = Case 
When Substring(@StrNo,1,1) = '2' Then IsNull(@StrRet,'') + 'Twenty '
When Substring(@StrNo,1,1) = '3' Then IsNull(@StrRet,'') + 'Thirty '
When Substring(@StrNo,1,1) = '4' Then IsNull(@StrRet,'') + 'Fourty '
When Substring(@StrNo,1,1) = '5' Then IsNull(@StrRet,'') + 'Fifty '
When Substring(@StrNo,1,1) = '6' Then IsNull(@StrRet,'') + 'Sixty '
When Substring(@StrNo,1,1) = '7' Then IsNull(@StrRet,'') + 'Seventy '
When Substring(@StrNo,1,1) = '8' Then IsNull(@StrRet,'') + 'Eighty '
When Substring(@StrNo,1,1) = '9' Then IsNull(@StrRet,'') + 'Ninty '
Else
@StrRet
End

If Convert(Numeric,Substring(@StrNo,1,1)) > 1
Begin
Select @StrRet = @StrRet + dbo.No2Word(Substring(@StrNo,2,1))
End

If Substring(@StrNo,1,1) = '0' And Convert(Numeric,Substring(@StrNo,2,1)) > 0
Begin
Select @StrRet = @StrRet + dbo.No2Word(Substring(@StrNo,2,1))
End
End
If @Len = 3
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Hundred '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,2))
End
If @Len = 4
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Thousand '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,3))
End
If @Len = 5
Begin
If Convert(Numeric,SubString(@StrNo,1,2)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Thousand '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,3))
End
If @Len = 6
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Lack '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,5))
End
If @Len = 7
Begin
If Convert(Numeric,SubString(@StrNo,1,2)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Lack '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,5))
End
If @Len = 8
Begin
If Convert(Numeric,SubString(@StrNo,1,1)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,1)) + ' Crore '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,2,7))
End
If @Len = 9
Begin
If Convert(Numeric,SubString(@StrNo,1,2)) > 0
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,1,2)) + ' Crore '
Select @StrRet = Isnull(@StrRet,'') + dbo.No2Word(Substring(@StrNo,3,8))
End
Return(@StrRet)
End


-- Select dbo.No2Word('123456789')

Rate

5 (1)

Share

Share

Rate

5 (1)