Solved

# Function/Procedure to spell out a decimal money amount

Posted on 2003-03-14
Medium Priority
1,509 Views
I want a function or procedure to spell out an amount of money, as for filling out the amount line on a check or receipt.

That is, I want to enter '12.23' and get back 'Twelve Dollars and Twenty Three Cents'.

Function should work up through the billions of dollars - Microsoft offers VB logic for this as the 'SpellNumber' routine, but I need it as a user defined SQL Server 2000 function.

I have no need to support fractional cents.

Points for either working code or for a pointer to same - I can't believe this is not out there already, but my searches have come back dry.
0
Question by:psmith789
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 2

Expert Comment

ID: 8139544
0

LVL 2

Expert Comment

ID: 8139551
0

LVL 3

Accepted Solution

Moliere earned 2000 total points
ID: 8139610
I am not sure where I found this code originally, but it works up to 999,999,999,999.99. Since it uses BigInt, it could go higher but modifications would be needed.

Create Function SpellOut(@Num Float)
Returns Varchar(2000)
As
begin
Declare @WORD1 Varchar(200),@WORD2 Varchar(200),@WORD3 Varchar(200),@WORD4 Varchar(200)
Declare @VT    BigInt,     @HEAD  BigInt,     @TERM  BigInt
Declare @TAIL   Float
Select
@WORD1 = 'ZERO ONE  TWO  THREEFOUR FIVE SIX  SEVENEIGHTNINE ',
@WORD2 = 'TEN      ELEVEN   TWELVE   THIRTEEN FOURTEEN FIFTEEN  SIXTEEN  SEVENTEENEIGHTEEN NINETEEN ',
@WORD3 = 'TWENTY THIRTY FORTY  FIFTY  SIXTY  SEVENTYEIGHTY NINETY ',
@WORD4 = '        THOUSANDMILLION BILLION ',
@STAIL = 'ONLY',
@TAIL  = 0,
@S     ='',
@STAIL ='',
if @Num <>Floor(@Num) --not a BigInt
begin
Set @TAIL = (@Num - Floor(@Num)) * 100
if  @TAIL - Floor(@TAIL) > 0.001
Set  @TAIL = Floor(@TAIL) + 1
else
Set  @TAIL= Floor(@TAIL)
if    @TAIL >= 20
begin
Select @S= RTrim(LTRIM(SubString(@WORD3, Convert(int,@TAIL / 9.99) * 7 - 13, 7))),
@TAIL=Floor(@TAIL)-Floor(Floor(@TAIL)/10)*10
if @TAIL > 0.001
begin
Select @STAIL=RTrim(LTRIM(SubString(@WORD1, Convert(int,@TAIL * 5 + 1), 5)) + ' ' + @STAIL),
@S= RTRIM(LTRIM(@S))+ ' ' + @STAIL
end
else
Select @S=RTRIM(LTRIM(@S)) + ' ' + @STAIL
Select @STAIL= @S, @S= ''
end
else
if @TAIL >= 10
Set @STAIL=RTRIM(LTRIM(SubString(@WORD2, (Convert(int,@TAIL) - 10) * 9 + 1, 9))) + ' ' + @STAIL
else
Set @STAIL=RTRIM(LTRIM(SubString(@WORD1, Convert(int,@TAIL) * 5 + 1, 5))) + ' ' + @STAIL
Set  @STAIL= 'AND ' +LTRIM(@STAIL)+' CENTS'
end;
begin
if @VT > 0
Set @TAIL= Floor(@VT / 100)
if @TAIL > 0
Select @SHEAD=RTRIM(LTrim(@SHEAD)) + ' ' +RTRIM(LTrim(SubString(@WORD1, Convert(int,@TAIL * 5 + 1), 5))) + ' HUNDRED'
Set       @TAIL = Floor(@VT)-Floor(@VT/100)*100
if @TAIL >= 20
begin
Select @SHEAD=RTRIM(LTrim(@SHEAD)) + ' ' +SubString(@WORD3, (Convert(int,@TAIL / 10) - 2) * 7 + 1, 7),
@TAIL =Floor(@TAIL)-Floor(@TAIL/10)*10
if @TAIL > 0
Set @SHEAD=RTRIM(Ltrim(@SHEAD)) + ' ' + SubString(@WORD1, Convert(int,@TAIL) * 5 + 1, 5)
end
else if @TAIL >= 10
Set @SHEAD=RTRIM(Ltrim(@SHEAD)) + ' ' +SubString(@WORD2, (Convert(int,@TAIL) - 10) * 9 + 1, 9)
else if @TAIL > 0
Set @SHEAD=RTRIM(Ltrim(@SHEAD)) + ' ' + SubString(@WORD1,Convert(int,@TAIL * 5) + 1, 5)
Select @SHEAD=RTRIM(Ltrim(@SHEAD)) + ' ' +SubString(@WORD4, Convert(int,@TERM * 8) + 1, 8),
@TERM =@TERM + 1,
@S    =RTRIM(Ltrim(@SHEAD)) + ' ' + @S,
end
Select @S=RTRIM(Ltrim(@S)) + ' DOLLARS ' + @STAIL
Return @S
end
go
0

LVL 69

Expert Comment

ID: 8140217
Here's another way using two functions if you don't like those:

--SELECT dbo.SpellDecimal(.75)
CREATE FUNCTION dbo.SpellDecimal (
@amount DECIMAL(12,2)
)
RETURNS VARCHAR(200)
AS
BEGIN --FUNCTION

--DECLARE @amount DECIMAL(12,2)
--SET @amount = 9876543210.17

DECLARE @result VARCHAR(200)
DECLARE @keywordSwi BIT
DECLARE @cents TINYINT
DECLARE @amt BIGINT

SET @amt = ROUND(@amount, 0, -1)
SET @cents = (@amount - @amt) * 100
SET @result = ''

--check for less than \$1
IF @amt < 1.00
SELECT @result = @result + dbo.Number99ToWord(0) + ' '
--check for billions
IF @amt > 999999999
BEGIN
SELECT @result = @result + dbo.Number99ToWord(@amt / 1000000000) + ' Billion '
SET @amt = @amt % 1000000000
END --IF
--check for hundreds of millions
SET @keywordSwi = 0
IF @amt > 99999999
BEGIN
SET @keywordSwi = 1
SELECT @result = @result + dbo.Number99ToWord(@amt / 100000000) + ' Hundred '
SET @amt = @amt % 100000000
END --IF
--check for millions
IF @amt > 999999
BEGIN
SET @keywordSwi = 1
SELECT @result = @result + dbo.Number99ToWord(@amt / 1000000)
SET @amt = @amt % 1000000
END --IF
IF @keywordSwi > 0
BEGIN
SET @result = @result + ' Million '
SET @keywordSwi = 0
END --IF
--check for hundreds of thousands
IF @amt > 99999
BEGIN
SET @keywordSwi = 1
SELECT @result = @result + dbo.Number99ToWord(@amt / 100000) + ' Hundred '
SET @amt = @amt % 100000
END --IF
--check for thousands
IF @amt > 999
BEGIN
SET @keywordSwi = 1
SELECT @result = @result + dbo.Number99ToWord(@amt / 1000)
SET @amt = @amt % 1000
END --IF
IF @keywordSwi > 0
BEGIN
SET @result = @result + ' Thousand '
SET @keywordSwi = 0
END --IF
--check for hundreds
IF @amt > 99
BEGIN
SELECT @result = @result + dbo.Number99ToWord(@amt / 100) + ' Hundred '
SET @amt = @amt % 100
END --IF
--check for other dollars
IF @amt > 0.99
BEGIN
SELECT @result = @result + dbo.Number99ToWord(@amt) + ' '
END --IF
IF @result = 'One'
SET @result = @result + 'Dollar '
ELSE
SET @result = @result + 'Dollars '
SET @result = @result + 'and '
SELECT @result = @result + dbo.Number99ToWord(@cents) + ' Cents'
--SELECT @result
RETURN @result
END --FUNCTION

--SELECT dbo.NumberToWord(1)
CREATE FUNCTION dbo.Number99ToWord (
@amount TINYINT
)
RETURNS VARCHAR(20)
AS
BEGIN --FUNCTION

--DECLARE @amount TINYINT
--SET @amount = 0

DECLARE @result VARCHAR(20)
SET @result = ''
IF @amount = 0.00
SET @result = 'Zero'
ELSE
IF @amount BETWEEN 11 AND 19
BEGIN
SET @result = RTRIM(SUBSTRING(
'Eleven   Twelve   Thirteen Fourteen Fifteen  Sixteen  SeventeenEighteen Nineteen',
((@amount - 10) - 1) * 9 + 1, 9))
END --IF
ELSE
BEGIN
IF @amount > 9
SET @result = RTRIM(SUBSTRING(
'Ten    Twenty Thirty Forty  Fifty  Sixty  SeventyEighty Ninety',
((@amount / 10) - 1) * 7 + 1, 7))
IF @amount % 10 <> 0
SET @result = @result + CASE WHEN @result <> '' THEN '-' ELSE '' END +
RTRIM(SUBSTRING('One  Two  ThreeFour Five Six  SevenEightNine',
((@amount % 10) - 1) * 5 + 1, 5))
END --ELSE
--SELECT @result
RETURN @result
END --FUNCTION
0

LVL 1

Author Comment

ID: 8142724
Moliere takes the points - advantage was encapsulating this in a single function. I will probably tweak slightly to return 'Dollar' and 'Cent' when there are only one of them, as the response following his did for dollars (but not for cents).

I'll also go to mixed case, but that is a quibble, and not something I specified.

I especially appreciate the implicit lesson in how to use an implicit array in an environment that does not support them explicitly.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
###### Suggested Courses
Course of the Month8 days, 12 hours left to enroll