?
Solved

Function/Procedure to spell out a decimal money amount

Posted on 2003-03-14
5
Medium Priority
?
1,509 Views
Last Modified: 2008-02-01
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
Comment
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
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:stiemark
ID: 8139544
0
 
LVL 2

Expert Comment

by:stiemark
ID: 8139551
0
 
LVL 3

Accepted Solution

by:
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 @S     Varchar(200),@STAIL Varchar(200),@SHEAD 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 ='',
  @SHEAD =''  
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;
  Select @HEAD= Floor(@Num),@TERM=0
  while  @HEAD > 0
  begin
   Select @VT  =@HEAD-Floor(@HEAD/1000)*1000,
          @HEAD= Floor(@HEAD / 1000)
    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,
           @SHEAD= ''
  end
 Select @S=RTRIM(Ltrim(@S)) + ' DOLLARS ' + @STAIL
  Return @S
end
go
0
 
LVL 69

Expert Comment

by:Scott Pletcher
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

by:psmith789
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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

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

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question