Functio to get Amount in words.

Question

Functio to get Amount in words.

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

My guess would be that it is the SUBSTRING()'s in your ON clauses.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Chris Morris (11/26/2008)

Chris, the link in your signature is broken. You'r missing the right hand bracket on the first instance of the URL IFCode ShortCut.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

rbarryyoung (11/26/2008)
My guess would be that it is the SUBSTRING()'s in your ON clauses.

You're spot on. It works fine in 2k, I should have tested it in 2k5 :blush:. The problem is with the first join (hundreds):

LEFT JOIN @tblNum h ON number < 15 AND SUBSTRING(@numberstr, number-2, 1) BETWEEN h.Num AND h.MaxNum

In 2k5, the substring is evaluated even when number = 15, which returns the decimal point from the substring expression.

So here's a 2k5-friendly version:

[font="Courier New"]ALTER FUNCTION dbo.ToWords(@TheNumber DECIMAL (15,2), @CurrencyName VARCHAR(20), @JoinWord VARCHAR(20), @CentsName VARCHAR(20))

RETURNS VARCHAR(200)

-- USAGE: SELECT dbo.ToWords(1.01, 'dollar(s)', 'and', 'cent(s)')

-- MAX. VALUE: 999999999999.99 (twelve nines before the decimal point)

BEGIN

DECLARE @numberstr CHAR (15), @NewString VARCHAR(200)

SET @numberstr = STR(@TheNumber, 15, 2)

SET @NewString = ''

DECLARE @tblNum TABLE(Num BIGint, MaxNum BIGint, NumStr VARCHAR(20))

INSERT INTO @tblNum

SELECT 1, 1, 'one' UNION ALL SELECT 2, 2, 'two' UNION ALL

SELECT 3, 3, 'three' UNION ALL SELECT 4, 4, 'four' UNION ALL

SELECT 5, 5, 'five' UNION ALL SELECT 6, 6, 'six' UNION ALL

SELECT 7, 7, 'seven' UNION ALL SELECT 8, 8, 'eight' UNION ALL

SELECT 9, 9, 'nine' UNION ALL SELECT 10, 10, 'ten' UNION ALL

SELECT 11, 11, 'eleven' UNION ALL SELECT 12, 12, 'twelve' UNION ALL

SELECT 13, 13, 'thirteen' UNION ALL SELECT 14, 14, 'fourteen' UNION ALL

SELECT 15, 15, 'fifteen' UNION ALL SELECT 16, 16, 'sixteen' UNION ALL

SELECT 17, 17, 'seventeen' UNION ALL SELECT 18, 18, 'eighteen' UNION ALL

SELECT 19, 19, 'nineteen' UNION ALL SELECT 20, 29, 'twenty' UNION ALL

SELECT 30, 39, 'thirty' UNION ALL SELECT 40, 49, 'forty' UNION ALL

SELECT 50, 59, 'fifty' UNION ALL SELECT 60, 69, 'sixty' UNION ALL

SELECT 70, 79, 'seventy' UNION ALL SELECT 80, 89, 'eighty' UNION ALL

SELECT 90, 99, 'ninety' UNION ALL SELECT 100, 999, 'hundred' UNION ALL

SELECT 1000, 999999, 'thousand' UNION ALL SELECT 1000000, 999999999, 'million' UNION ALL

SELECT 1000000000, 999999999999, 'billion'

SELECT @NewString = @NewString

+ CASE WHEN h.NumStr IS NULL THEN '' ELSE h.NumStr + ' ' + c.NumStr + ' ' END

+ CASE WHEN number < 15 AND h.NumStr IS NOT NULL AND (t.NumStr IS NOT NULL OR u.NumStr IS NOT NULL) THEN @JoinWord + ' ' ELSE '' END

+ CASE WHEN number < 15 AND t.NumStr IS NOT NULL THEN t.NumStr + ' ' ELSE '' END

+ CASE WHEN number < 15 AND u.NumStr IS NOT NULL THEN u.NumStr + ' ' ELSE '' END

+ CASE WHEN number = 12 THEN @CurrencyName + ' ' ELSE '' END

+ CASE WHEN number = 15 AND (t.NumStr IS NOT NULL OR u.NumStr IS NOT NULL) THEN @JoinWord + ' ' ELSE '' END

+ CASE WHEN number = 15 AND t.NumStr IS NOT NULL THEN t.NumStr + ' ' ELSE '' END

+ CASE WHEN number = 15 AND u.NumStr IS NOT NULL THEN u.NumStr + ' ' ELSE '' END

+ CASE WHEN number = 15 AND (t.NumStr IS NOT NULL OR u.NumStr IS NOT NULL) THEN @CentsName + ' ' ELSE '' END

+ CASE WHEN k.NumStr IS NULL THEN '' ELSE k.NumStr + ', ' END

FROM (SELECT 3 AS number UNION ALL SELECT 6 UNION ALL SELECT 9 UNION ALL SELECT 12 UNION ALL SELECT 15) n

LEFT JOIN @tblNum h ON number < 15 AND REPLACE(SUBSTRING(@numberstr, number-2, 1), '.', '') BETWEEN h.Num AND h.MaxNum

LEFT JOIN @tblNum t ON SUBSTRING(@numberstr, number-1, 2) BETWEEN t.Num AND t.MaxNum AND t.Num > 9

LEFT JOIN @tblNum u ON SUBSTRING(@numberstr, number, 1) BETWEEN u.Num AND u.MaxNum AND NOT SUBSTRING(@numberstr, number-1, 2) BETWEEN 10 AND 19

LEFT JOIN @tblNum c ON number < 15 AND c.Num = 100

LEFT JOIN @tblNum k ON number < 12 AND k.Num = POWER(10, 12-number) AND k.Num < @TheNumber

RETURN @NewString

END

GO

--SELECT dbo.ToWords(number, 'dollar(s)', 'and', 'cent(s)')

--FROM Numbers

--WHERE number < 30000

-- about a minute

SELECT dbo.ToWords(999999999999.99, 'dollar(s)', 'and', 'cent(s)')

-- nine hundred and ninety nine billion, nine hundred and ninety nine million,

-- nine hundred and ninety nine thousand, nine hundred and ninety nine dollar(s) and ninety nine cent(s)

SELECT dbo.ToWords(999999999999.99, 'pounds', 'and', 'pence')

-- nine hundred and ninety nine billion, nine hundred and ninety nine million,

-- nine hundred and ninety nine thousand, nine hundred and ninety nine pounds and ninety nine pence

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jeff Moden (11/26/2008)
Chris Morris (11/26/2008)
Chris, the link in your signature is broken. You'r missing the right hand bracket on the first instance of the URL IFCode ShortCut.

Thanks Jeff, it's fixed now. Dunno if it's restrictions on the work pc's here but the sig editor has a mind of it's own.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Chris Morris (11/27/2008)Dunno if it's restrictions on the work pc's here but the sig editor has a mind of it's own.

I hear that. It took me forever to get my signature right. :crazy:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Here's a 2k/2k5-friendly version with a permanent lookup table. It translates 30,000 numbers from a standard tally table, in both SQL Server versions, in about 4 seconds.

[font="Courier New"]IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[NumbersAsWords]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[NumbersAsWords]

CREATE TABLE [dbo].[NumbersAsWords] (

[Num] [bigint] NULL ,

[MaxNum] [bigint] NULL ,

[NumStr] [varchar] (20) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

CREATE CLUSTERED INDEX [Num] ON [dbo].[NumbersAsWords]([Num]) ON [PRIMARY]

CREATE INDEX [MaxNum] ON [dbo].[NumbersAsWords]([MaxNum]) ON [PRIMARY]

INSERT INTO dbo.NumbersAsWords