CREATE Function [dbo].[fConvertDigit](@decNumber VARCHAR(1))RETURNS VARCHAR(6)
AS
BEGIN
DECLARE
@strWords VARCHAR(6)
SELECT @strWords = CASE @decNumber
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
RETURN
@strWords
END
CREATE Function [dbo].[fConvertTens](@decNumber VARCHAR(2))RETURNS VARCHAR(30)
AS
BEGIN
DECLARE
@strWords varchar(30)
--Is value between
10 And 19?
IF LEFT(@decNumber, 1) = 1
BEGIN
SELECT @strWords = CASE @decNumber
WHEN '10' THEN 'Ten'
WHEN '11' THEN 'Eleven'
WHEN '12' THEN 'Twelve'
WHEN '13' THEN 'Thirteen'
WHEN '14' THEN 'Fourteen'
WHEN '15' THEN 'Fifteen'
WHEN '16' THEN 'Sixteen'
WHEN '17' THEN 'Seventeen'
WHEN '18' THEN 'Eighteen'
WHEN
'19' THEN 'Nineteen'
END
END
ELSE -- otherwise it's
between 20 And 99.
BEGIN
SELECT @strWords = CASE LEFT(@decNumber, 1)
WHEN '0' THEN ''
WHEN '2' THEN 'Twenty '
WHEN '3' THEN 'Thirty '
WHEN '4' THEN 'Forty '
WHEN '5' THEN 'Fifty '
WHEN '6' THEN 'Sixty '
WHEN '7' THEN 'Seventy '
WHEN '8' THEN 'Eighty '
WHEN '9' THEN 'Ninety '
END
SELECT
@strWords = @strWords +
dbo.fConvertDigit(RIGHT(@decNumber, 1)) --Convert
ones place digit.
END
RETURN RTRIM(@strWords)
END
CREATE FUNCTION [dbo].[cFunction_AmountInWords] (@decNumber DECIMAL(18, 2)) RETURNS VARCHAR(3000)
AS
BEGIN
DECLARE
@strNumber varchar(100),
@strRupees varchar(200),
@strPaise varchar(100),
@strWords varchar(300),
@intIndex integer
-- For Paise
SET @strNumber
= CAST(@decNumber AS VARCHAR(100))
SET @intIndex = CHARINDEX('.', @strNumber)
IF @intIndex > 0
BEGIN
SET @strPaise = dbo.fConvertTens(RIGHT(@strNumber,LEN(@strNumber) - @intIndex))
SET @strNumber = LEFT(@strNumber,
@intIndex -1
)
IF LEN(@strPaise) > 0 SELECT
@strPaise = @strPaise +
' Paise'
END
-- Rupees
SET @strRupees
= ''
SET @intIndex=LEN(@strNumber)
WHILE(@intIndex>0)
BEGIN
SET @strRupees =
@strRupees +
(CASE WHEN @intIndex > 8
THEN dbo.cFunction_AmountInWords(CONVERT(INT,LEFT(@strNumber , LEN(@strNumber)-7))) + ' Crores '
WHEN
@intIndex = 8 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertDigit(LEFT(@strNumber,1)) + (CASE WHEN LEFT(@strNumber,1) <> '1' THEN ' Crore ' ELSE ' Crores ' END)
WHEN
@intIndex = 7 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertTens(LEFT(@strNumber,2)) + ' Lacs '
WHEN
@intIndex = 6 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertDigit(LEFT(@strNumber,1)) + + (CASE WHEN LEFT(@strNumber,1) <> '1' THEN ' Lac ' ELSE ' Lacs ' END)
WHEN
@intIndex = 5 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertTens(LEFT(@strNumber,2)) + ' Thousand '
WHEN
@intIndex = 4 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertDigit(LEFT(@strNumber,1)) + ' Thousand '
WHEN
@intIndex = 3 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertDigit(LEFT(@strNumber,1)) + ' Hundred '
WHEN
@intIndex = 2 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertTens(LEFT(@strNumber,2))
WHEN
@intIndex = 1 AND
LEFT(@strNumber,1) <> '0' THEN dbo.fConvertDigit(LEFT(@strNumber,1))
ELSE ''
END)
SET @intIndex=@intIndex
-
(CASE WHEN @intIndex > 8
THEN (@intIndex
- 7)
WHEN (@intIndex = 7 OR @intIndex = 5 OR @intIndex = 2) AND LEFT(@strNumber,1) <> '0' THEN 2
ELSE 1
END)
SET @strNumber = RIGHT(@strNumber,@intIndex)
END
-- For Return
IF(LEN(@strPaise)<>0)
BEGIN
IF LEN(@strRupees)>0 SET @strRupees=@strRupees + ' And '
END
SET @strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
RETURN
@strWords
--
---------------------
END
create proc fnNumToWords
@Number Numeric(18,2)
as
BEGIN
create table
#M_Words (Code numeric(9) identity,WNumber int Default 0,Wwords varchar(100) Default '')
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)))
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 --' Tens'
End
If @IUnt > 0
Begin
Select
@Wwords = ''
Select @Wwords =
Wwords From #M_Words Where
WNumber = @IUnt
Select
@SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
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 --' Tens'
End
If @IUnt > 0
Begin
Select
@Wwords = ''
Select
@Wwords = Wwords From
#M_Words Where WNumber =
@IUnt
Select
@SNumToWords = @SNumToWords + Space(1) + @Wwords --' Unit'
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 --' Tens'
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 = @SNumToWords + Space(1) + 'Rupees' --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 =
@SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
End
Else
Begin
Select @Wwords = ''
Select @Wwords = Wwords From
#M_Words Where WNumber =
@ILacs
Select @SNumToWords
= @SNumToWords +
@Wwords + Space(1) + 'Rupee'
End
End
select @Number
End
