Convert Amount in Words using SQL Function

IN SQL Server 2000

*******FUNCTION********
Create scalar function on database side:

Pass Number and conversion of Paise to words required or not (Y/N) to the Function below


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 = @SNumToWords + Space(1) + 'Rupees' --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 = @SNumToWords + Space(1) + @Wwords + Space(1) + 'Rupees'
End
Else
Begin
Select @Wwords = ''
Select @Wwords = Wwords From M_Words Where WNumber = @ILacs
--Print @Wwords
Select @SNumToWords = @SNumToWords + @Wwords + Space(1) + 'Rupee'
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

--Print LTrim(RTrim(@SNumToWords))
Return LTrim(RTrim(@SNumToWords))

End



2. Then Create Below Table (M_Words) on batabase side :

***********RELATED TABLE******************

M_Words

Code numeric 9 Identity = YES
WNumber int 4 Default Value = (0)
Wwords varchar 50 Default Value = (' ')


***********DATA IN TABLE M_Words****************

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 Nineteen
21 20 Twenty
22 30 Thirty
23 40 Forty
24 50 Fifty
25 60 Sixty
26 70 Seventy
27 80 Eighty
28 90 Ninety



Comments

Author: sourabh malani15 Jul 2010 Member Level: Bronze   Points : 2

-- Create employee table and insert some records

create table employee
(
employee_id int identity(1,1),
employee_name nvarchar(50),
salary decimal(10,2)
)

-- Create procedure to display salary in words and execute procedure
create procedure salary_in_words
@employee_id int
as

DECLARE @i int, @temp char(1), @s VARCHAR(20), @result VARCHAR(255),@n bigint /*input*/

select @n = salary from employee_master
where employee_id=@employee_id;

SELECT @s=convert(varchar(20), @n)
SELECT @i=LEN(@s)
SELECT @result=''
WHILE (@i>0)
BEGIN
SELECT @temp=(SUBSTRING(@s,@i,1))
print 'the temp is ' + @temp
IF ((LEN(@s)-@i) % 3)=1
IF @temp='1'
begin
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN 'ten'
WHEN '1' THEN 'eleven'
WHEN '2' THEN 'twelve'
WHEN '3' THEN 'thirteen'
WHEN '4' THEN 'fourteen'
WHEN '5' THEN 'fifteen'
WHEN '6' THEN 'sixteen'
WHEN '7' THEN 'seventeen'
WHEN '8' THEN 'eighteen'
WHEN '9' THEN 'nineteen'
END+' '+CASE
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=6) THEN 'lakhs '
WHEN ((LEN(@s)-@i)=8) THEN 'crores '
WHEN ((LEN(@s)-@i)=10) THEN 'million '
WHEN ((LEN(@s)-@i)=12) THEN 'billion '
ELSE ''
END+@result


end
ELSE
BEGIN
SELECT @result = CASE
WHEN ((LEN(@s)-@i)=1) and @temp > 0 THEN 'hundred '
WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
WHEN ((LEN(@s)-@i)=6) THEN 'lakhs '
WHEN ((LEN(@s)-@i)=8) THEN 'crores '
WHEN ((LEN(@s)-@i)=10) THEN 'million '
WHEN ((LEN(@s)-@i)=12) THEN 'billion '
ELSE ''
END + ' ' + CASE (SUBSTRING(@s,@i+1,1))
WHEN '0' THEN ''
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'
END+' '+@result
SELECT @result=CASE @temp
WHEN '0' THEN ''
WHEN '1' THEN 'ten'
WHEN '2' THEN 'twenty'
WHEN '3' THEN 'thirty'
WHEN '4' THEN 'fourty'
WHEN '5' THEN 'fifty'
WHEN '6' THEN 'sixty'
WHEN '7' THEN 'seventy'
WHEN '8' THEN 'eighty'
WHEN '9' THEN 'ninety'
END+' '+@result



END
IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)
BEGIN
SELECT @result=CASE @temp
WHEN '0' THEN ''
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'
END +' '+CASE
WHEN (@s='0') THEN 'zero'
ELSE ''
END + CASE
WHEN ((LEN(@s)-@i)=3) THEN 'thousand '
WHEN ((LEN(@s)-@i)=5) THEN 'lakhs '
WHEN ((LEN(@s)-@i)=7) THEN 'crores '
WHEN ((LEN(@s)-@i)=9) THEN 'million '
WHEN ((LEN(@s)-@i)=11) THEN 'billion '
ELSE ''
END+ @result


END
SELECT @i=@i-1
END
select REPLACE(@result,' ',' ')+ 'rupees only'


Thanks & Regards,
Sourabh S. Malani



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: