Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1534
  • Last Modified:

Function/Procedure to spell out a decimal money amount

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
psmith789
Asked:
psmith789
1 Solution
 
stiemarkCommented:
0
 
MoliereCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
psmith789Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now