Currency amount to words - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Currency amount to words

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-27 : 11:03:40

I have a table with two columns. One is a money column, the other a varchar column. The varchar column is all null entries.

Is there a way I can update the varchar column with the monetary amount in words?

So I would get:

money words
----- -----
20.00 Twenty Dollars
15.50 Fifteen Dollars and Fifteen Cents
11.00 Eleven Dollars

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-27 : 11:11:26

you know how to UPDATE a value, right? Is what you need help with deriving the word values based on the money value?

If so, there is nothing built in to convert a money value to words. You would need to write your own function or find one that someone else has already done. How big a value do you need to account for? ie: 1,234,456,500.92 ? or just what you have in your checking account: $12.67 ? :)

EDIT:
if you tried searching first you may have found these:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86067
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11157

Be One with the Optimizer
TG

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-27 : 11:25:04

Many thanks for your reply.

parody
Posting Yak Master

111 Posts

Posted - 2009-10-29 : 12:08:42

Hi

Thought I would post my version. It may be more flexible than the other two, could be expanded easily, includes multiple currencies (and easy to add more), plus the logic can be relatively easily changed to accommodate where you want the and's to go, whether you want zero's converted etc - everyone says a number a different way or its requires different ways if its for cheques, letters etc.

Its based around one function which works on sets of 3 digits at a time along with a group indicator for the units and currency indicator. The main function concatenates them together and implements some logic for incorporating 'and', to ignore zero values etc.

CREATE FUNCTION [dbo].[udf_CurrencyToWordsSub](@Input varchar(3),@Group tinyint,@Currency tinyint)
RETURNS varchar(8000)
AS
BEGIN

SET @Input = right('000' + @Input,3)

DECLARE @Groups TABLE
([Group] tinyint
,Currency tinyint
,Word varchar(10))

INSERT INTO @Groups
SELECT 5,NULL,'Billion '
UNION
SELECT 4,NULL,'Million '
UNION
SELECT 3,NULL,'Thousand '
UNION
SELECT 2,1,'Pound# '
UNION
SELECT 2,2,'Euro# '
UNION
SELECT 2,3,'Dollar# '
UNION
SELECT 1,1,'Pence '
UNION
SELECT 1,2,'Cent~ '
UNION
SELECT 1,3,'Cent~ '

DECLARE @Numbers TABLE
(Number char(2)
,Word varchar(10))

INSERT INTO @Numbers
SELECT '01','One ' UNION SELECT '02','Two ' UNION SELECT '03','Three ' UNION SELECT '04','Four ' UNION SELECT '05','Five ' UNION SELECT '06','Six ' UNION SELECT '07','Seven ' UNION SELECT '08','Eight ' UNION SELECT '09','Nine ' UNION SELECT '10','Ten ' UNION
SELECT '11','Eleven ' UNION SELECT '12','Twelve ' UNION SELECT '13','Thirteen ' UNION SELECT '14','Fourteen ' UNION SELECT '15','Fifteen ' UNION SELECT '16','Sixteen ' UNION SELECT '17','Seventeen ' UNION SELECT '18','Eighteen ' UNION SELECT '19','Nineteen ' UNION SELECT '20','Twenty ' UNION
SELECT '30','Thirty ' UNION SELECT '40','Forty ' UNION SELECT '50','Fifty ' UNION SELECT '60','Sixty ' UNION SELECT '70','Seventy ' UNION SELECT '80','Eighty ' UNION SELECT '90','Ninety '

DECLARE @Output varchar(100)

SET @Output =
CASE --Hundreds
WHEN left(@Input,1) <> 0 THEN (SELECT Word FROM @Numbers WHERE Number = '0' + left(@Input,1)) + 'Hundred '
ELSE ''
END +
CASE --And
WHEN @Group <> 1 AND right(@Input,2) <> 0 AND @Input > 100 THEN 'And '
ELSE ''
END +
CASE --Units
WHEN @Input = 0 AND @Group = 1 THEN 'Zero '
WHEN right(@Input,2) BETWEEN 1 AND 20 THEN (SELECT Word FROM @Numbers WHERE Number = right(@Input,2))
WHEN right(@Input,2) > 20 THEN (SELECT Word FROM @Numbers WHERE Number = left(right(@Input,2),1) + '0') + ISNULL((SELECT Word FROM @Numbers WHERE Number = '0' + right(@Input,1)),'')
ELSE ''
END +
CASE
WHEN @Group = 2 OR (@Group <> 2 AND @Input <> 0) THEN (SELECT Word FROM @Groups WHERE [Group] = @Group AND ISNULL(Currency,@Currency) = @Currency)
ELSE ''
END

RETURN @Output

END

CREATE FUNCTION [dbo].[udf_CurrencyToWords] (@Input numeric(15,2),@Currency tinyint)
RETURNS varchar (8000)
AS
BEGIN

DECLARE @CharInput char(15)
--Pad the input
SET @CharInput = RIGHT('0000000000000' + convert(varchar(15),@Input),15)

DECLARE @Counter tinyint
SET @Counter = 1

DECLARE @InputSub varchar(3)
DECLARE @Group tinyint
DECLARE @Output varchar (8000)

--Get words for each group with some logic for concatenation
WHILE @Counter <= len(@CharInput)-2
BEGIN
SET @InputSub = replace(substring(@CharInput,@Counter,3),'.','')
SET @Group = (len(@CharInput)-@Counter+1)/3

SET @Output =
ISNULL(@Output,'')
+ CASE
WHEN (@Group = 1 AND @InputSub <> 0 AND @Input >= 1.00) OR (@Group = 2 AND @InputSub BETWEEN 1 AND 99 AND @Input > 1000) THEN 'And '
ELSE ''
END
+ CASE
WHEN (@Group = 1 AND @InputSub = 0 AND @Input >= 1.00) OR (@Group = 2 AND @InputSub = 0 AND @Input < 1.00) THEN ''
ELSE [dbo].[udf_CurrencyToWordsSub](@InputSub,@Group,@Currency)
END

SET @Counter = @Counter + 3
CONTINUE
END

--Fix plurals and return a plain Zero if required
SET @Output =
CASE
WHEN right(@CharInput,2) = '01' THEN replace(@Output,'~','')
ELSE replace(@Output,'~','s')
END
SET @Output =
CASE
WHEN @Input < 2 THEN replace(@Output,'#','')
ELSE replace(@Output,'#','s')
END

RETURN ltrim(rtrim(@Output))
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources