asked on

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.

stiemark

Check this out:
http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/181/fid/1776

stiemark

Or this:
https://www.experts-exchange.com/questions/20486161/Currency-to-written-out-string.html

ASKER CERTIFIED SOLUTION

Moliere

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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

psmith789

ASKER

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.