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)