Display numbers as words
John Beardsworth
These are for faxes authorising bank transfers and need to show both the
value ($2,001,020) and the value as words ("two million, one thousand etc).
I'm sure there must be a function somewhere to display numbers as words. Can
anyone point me where?
John
----------------------------------
John Beardsworth @ http://www.beardsworth.co.uk
developing photography
remove oohaah from email address for replies
----------------------------------
Hirantha S Hettiarachchi
http://www.xephon.com/oldcode/Q014A05
replace " with ' in the sp.
thanks to
Gunvant Patil
DBA (Microsoft Certified)
Silverline Industries Ltd (India)
--
-------------------------------------------
Hirantha S Hettiarachchi MCSD,MCDBA
Please reply only to the newsgroups.
"John Beardsworth" <jo...@OOHAAHbeardsworth.co.uk> wrote in message
news:a0snh6$h0f$1...@helle.btinternet.com...
Steve Kass
Here's another approach. This is set up for US names for numbers, so
you'll have to make some adjustments.
CREATE TABLE THOUSAND (
num int,
wrd varchar(30)
)
INSERT INTO THOUSAND VALUES (0,'')
INSERT INTO THOUSAND VALUES (1,'one')
INSERT INTO THOUSAND VALUES (2,'two')
INSERT INTO THOUSAND VALUES (3,'three')
INSERT INTO THOUSAND VALUES (4,'four')
INSERT INTO THOUSAND VALUES (5,'five')
INSERT INTO THOUSAND VALUES (6,'six')
INSERT INTO THOUSAND VALUES (7,'seven')
INSERT INTO THOUSAND VALUES (8,'eight')
INSERT INTO THOUSAND VALUES (9,'nine')
INSERT INTO THOUSAND VALUES (10,'ten')
INSERT INTO THOUSAND VALUES (11,'eleven')
INSERT INTO THOUSAND VALUES (12,'twelve')
INSERT INTO THOUSAND VALUES (13,'thirteen')
INSERT INTO THOUSAND VALUES (14,'fourteen')
INSERT INTO THOUSAND VALUES (15,'fifteen')
INSERT INTO THOUSAND VALUES (16,'sixteen')
INSERT INTO THOUSAND VALUES (17,'seventeen')
INSERT INTO THOUSAND VALUES (18,'eighteen')
INSERT INTO THOUSAND VALUES (19,'nineteen')
INSERT INTO THOUSAND VALUES (20,'twenty')
INSERT INTO THOUSAND VALUES (30,'thirty')
INSERT INTO THOUSAND VALUES (40,'forty')
INSERT INTO THOUSAND VALUES (50,'fifty')
INSERT INTO THOUSAND VALUES (60,'sixty')
INSERT INTO THOUSAND VALUES (70,'seventy')
INSERT INTO THOUSAND VALUES (80,'eighty')
INSERT INTO THOUSAND VALUES (90,'ninety')
INSERT INTO THOUSAND
SELECT A.num+B.num, A.wrd+'-'+B.wrd
FROM (SELECT * FROM THOUSAND WHERE num BETWEEN 20 AND 90) A
CROSS JOIN (SELECT * FROM THOUSAND WHERE num BETWEEN 1 AND 9) B
INSERT INTO THOUSAND
SELECT A.num*100+B.num, RTRIM(A.wrd+' '+'hundred '+B.wrd)
FROM (SELECT * FROM THOUSAND WHERE num BETWEEN 1 AND 9) A
CROSS JOIN THOUSAND B
GO
CREATE FUNCTION NameNum (
@n bigint
) returns varchar(200) as begin
if @n = 0
return 'zero'
declare @s varchar(12)
set @s = right(replicate('0',12)+cast(@n as varchar(12)),12)
declare @w varchar(200)
set @w = ''
if left(@s,3) > 0
set @w = @w + (select wrd from thousand where num=left(@s,3))
+ ' billion '
if left(right(@s,9),3) > 0
set @w = @w + (select wrd from thousand where num=left(right(@s,9),3))
+ ' million '
if left(right(@s,6),3) > 0
set @w = @w + (select wrd from thousand where num=left(right(@s,6),3))
+ ' thousand '
if right(@s,3) > 0
set @w = @w + (select wrd from thousand where num=right(@s,3))
return @w
end
GO
CREATE TABLE #John (
n bigint
)
INSERT INTO #John VALUES(1443)
INSERT INTO #John VALUES(1443000)
INSERT INTO #John VALUES(11000443)
INSERT INTO #John VALUES(9876543211)
INSERT INTO #John VALUES(900000911)
INSERT INTO #John VALUES(9000000000)
INSERT INTO #John VALUES(0)
SELECT n, dbo.NameNum(n) as Number from #John
DROP TABLE THOUSAND
DROP FUNCTION NameNum
DROP TABLE #John
Steve Kass
Drew University
Bruce Ferguson
ToWords({table.amount},decimals)
If you want the cents in words, you will need to split that out and do
seperately.
ToWords(Truncate({table.amount},0),0) + " dollars " +
ToWords(Remainder({table.amount} * 100,100),0) + " cents"
"John Beardsworth" <jo...@OOHAAHbeardsworth.co.uk> wrote in message
news:a0snh6$h0f$1...@helle.btinternet.com...
John Beardsworth
I'll try out both ways. Must admit I'm a little surprised there's not some
built in function!
John
----------------------------------
John Beardsworth @ http://www.beardsworth.co.uk
developing photography
remove oohaah from email address for replies
----------------------------------
Steve Kass <sk...@drew.edu> wrote in message
news:3C31EF37...@drew.edu...
John Beardsworth
John
----------------------------------
John Beardsworth @ http://www.beardsworth.co.uk
developing photography
remove oohaah from email address for replies
----------------------------------
Bruce Ferguson <br...@chelseatech.co.nz.nospam> wrote in message
news:a0t9nm$s75$1...@news.wave.co.nz...
Joe Celko
your work, you might find it much faster to build a table of numbers and
words and do a join before you invoke a procedural solution. Say, 90%
of your work is with amounts under $10,000, you can do the join, then
invoke a procedure on the other 10% in a UNION ALL query.
You probably already heard the Dilbert story about this, but people get
confused when their check for $2500 is made out as "Two Thousand Five
Hundred Dollars" and they were expecting to get "Twenty-five Hundred
Dollars" instead. I wish that was a joke ...
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Stu
>If you are dealing with amounts under a reasonable limit
for most of
>your work, you might find it much faster to build a table
of numbers and
>words and do a join before you invoke a procedural
solution. Say, 90%
>of your work is with amounts under $10,000, you can do
the join, then
>invoke a procedure on the other 10% in a UNION ALL query.
<snip>
>--CELKO--
You can handle any amount without procedural code or
UNIONS.
1. Create a table
CREATE TABLE Numbers
(Number int PRIMARY KEY,
Words varchar(30) NOT NULL)
2. Populate it with the literal strings of all numbers
from 0 to 999. (See the really ugly procedural code at the
end of the message, which I'm sure could be improved.)
3. Assuming that your range is 1 - 999,999,999 use the
following query; it should be obvious how to extend it for
larger numbers.
-- -------------------------------------------------------
SELECT
CASE
WHEN @Num < 1000 THEN (SELECT Words FROM Numbers WHERE
Number = @Num)
WHEN @Num < 1000000 THEN
(SELECT Words FROM Numbers WHERE Number = @Num / 1000)
+ ' thousand ' +
(SELECT Words FROM Numbers WHERE Number = @Num % 1000)
WHEN @Num < 1000000000 THEN
(SELECT Words FROM Numbers WHERE Number = @Num /
1000000) + ' million ' +
(SELECT Words FROM Numbers WHERE Number = (@Num /
1000) % 1000) +
CASE WHEN (@Num / 1000) % 1000 > 0 THEN ' thousand '
ELSE '' END +
(SELECT Words FROM Numbers WHERE Number = @Num % 1000)
END
-- -----------------------------------------------------
-- Stu Bloom, MCSD
-- ugly procedural code, but only necessary to run one
-- time; could be improved with looped INSERTs and
-- multi-level CASEs replacing the updates
CREATE TABLE Numbers
( Number int PRIMARY KEY,
Words varchar(30) NOT NULL
)
DECLARE @Num int
SET @Num = 0
WHILE @Num <= 999
BEGIN
INSERT INTO Numbers VALUES (@Num, '')
IF @Num > 99
BEGIN
UPDATE Numbers SET Words =
CASE @Num / 100
WHEN 1 THEN 'one hundred'
WHEN 2 THEN 'two hundred'
WHEN 3 THEN 'three hundred'
WHEN 4 THEN 'four hundred'
WHEN 5 THEN 'five hundred'
WHEN 6 THEN 'six hundred'
WHEN 7 THEN 'seven hundred'
WHEN 8 THEN 'eight hundred'
ELSE 'nine hundred'
END
WHERE Number = @Num
END
IF @Num > 9
BEGIN
UPDATE Numbers SET Words = Words +
CASE (@Num % 100) / 10
WHEN 9 THEN ' ninety'
WHEN 8 THEN ' eighty'
WHEN 7 THEN ' seventy'
WHEN 6 THEN ' sixty'
WHEN 5 THEN ' fifty'
WHEN 4 THEN ' forty'
WHEN 3 THEN ' thirty'
WHEN 2 THEN ' twenty'
WHEN 1 THEN
CASE @Num % 100
WHEN 19
THEN ' nineteen'
WHEN 18
THEN ' eighteen'
WHEN 17
THEN ' seventeen'
WHEN 16
THEN ' sixteen'
WHEN 15
THEN ' fifteen'
WHEN 14
THEN ' fourteen'
WHEN 13
THEN ' thirteen'
WHEN 12
THEN ' twelve'
WHEN 11
THEN ' eleven'
ELSE ' ten'
END
ELSE ''
END
WHERE Number = @Num
END
IF @Num % 100 > 20
BEGIN
UPDATE Numbers SET Words = Words +
CASE (@Num % 100) % 10
WHEN 9 THEN '-nine'
WHEN 8 THEN '-eight'
WHEN 7 THEN '-seven'
WHEN 6 THEN '-six'
WHEN 5 THEN '-five'
WHEN 4 THEN '-four'
WHEN 3 THEN '-three'
WHEN 2 THEN '-two'
WHEN 1 THEN '-one'
ELSE ''
END
WHERE Number = @Num
END
IF @Num % 100 < 10
BEGIN
UPDATE Numbers SET Words = Words +
CASE @Num % 100
WHEN 9 THEN ' nine'
WHEN 8 THEN ' eight'
WHEN 7 THEN ' seven'
WHEN 6 THEN ' six'
WHEN 5 THEN ' five'
WHEN 4 THEN ' four'
WHEN 3 THEN ' three'
WHEN 2 THEN ' two'
WHEN 1 THEN ' one'
ELSE ''
END
WHERE Number = @Num
END
UPDATE Numbers SET Words = LTRIM(Words)
SET @Num = @Num + 1
END
Steve Kass
Does this do anything differently than the code I posted
yesterday for this?
Steve
Stu
"Steve Kass" <sk...@drew.edu> wrote in message
news:3C33736A...@drew.edu...
MShe...@compuserve.com
<71062...@compuserve.com> wrote:
>If you are dealing with amounts under a reasonable limit for most of
>your work, you might find it much faster to build a table of numbers and
>words and do a join before you invoke a procedural solution.
Faster still--don't use words at all. For writing checks, words
aren't necessary, at least in the USA.
--
Mike Sherrill
Information Management Systems
BP Margolin
I won't dispute you if you say that USA law does not actually require words
on checks, however I would point out the following:
If both words and numbers are present on a check, and the amounts
represented by the words and numbers disagree, then banks will honor the
words and not the numbers.
Numbers are a lot easier to alter than words, which is the explanation as to
why words are usually included on checks, and also the reason why banks will
honor words above numbers.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
<MShe...@compuserve.com> wrote in message
news:3c35c5c8....@news.compuserve.com...