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

# 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
1 Solution

Commented:
0

Commented:
0

Commented:
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

Senior 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

Author 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

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