Functio to get Amount in words.

Question

Post reply

Functio to get Amount in words.

Viewing 15 posts - 31 through 45 (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

Chris:

I just notice that you are putting "and" after your Hundreds, like this:

nine hundred and seventy seven thousand, six hundred and ninety two dollar(s)

Technically, this is incorrect, the word "and" should only be used wherever the decimal point occurs, like this:

nine hundred seventy seven thousand, six hundred ninety two dollars and twenty-two cents

[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: 1004534 More actions · Answer 2

rbarryyoung (11/27/2008)
Jeff:
Your function is called "NumberToWords" but you are calling "MoneyToWords", is this just a typo or am I missing something?

Heh... guilty as charged. The Phat-Phinger-Phairy made a trip to my house, today. He convinced me to rename the function, at the last minute, to be more generic, but forgot to tell me to change the test code before I posted it. 😛

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Well I did test them on my laptop. Chris's ran in 13 & 15 seconds, while Jeff's ran in 5.1 and 5.2 seconds.

[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 4

rbarryyoung (11/27/2008)
Well I did test them on my laptop. Chris's ran in 13 & 15 seconds, while Jeff's ran in 5.1 and 5.2 seconds.

Testing in my reporting environment yields very similar results:

SELECT dbo.NumberToWords (t.number-15000,'Dollar(s)','And','Cent(s)')

FROM dbo.Numbers t

WHERE t.number BETWEEN 1 AND 30000

-- 4.7 seconds (avg of 5 consecutive runs, break, 3 consecutive runs)

SELECT dbo.NumberToWords (t.number-15000.03,NULL,'And','/100 Dollar(s)')

FROM dbo.Numbers t

WHERE t.number BETWEEN 1 AND 30000

-- 5.7 seconds (avg of 5 consecutive runs, break, 3 consecutive runs)

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

FROM Numbers

WHERE number < 30000

-- 9.2 seconds (avg of 5 consecutive runs, break, 3 consecutive runs)

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

FROM Numbers

WHERE number BETWEEN 970001 AND 1000000

-- 10.6 seconds (avg of 5 consecutive runs, break, 3 consecutive runs)

I like the new table structure, Jeff. Makes a lot more sense.

^{“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 5

rbarryyoung (11/27/2008)
Chris:
I just notice that you are putting "and" after your Hundreds, like this:
nine hundred and seventy seven thousand, six hundred and ninety two dollar(s)
Technically, this is incorrect, the word "and" should only be used wherever the decimal point occurs, like this:
nine hundred seventy seven thousand, six hundred ninety two dollars and twenty-two cents

US and UK format, Barry, but fair enough - if a function accepts ['dollars', 'and', 'cents'] as parameters, then the resultant string should place the join-word only where it's expected to be!

^{“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 6

Really? "One hundred and One" is acceptable in the UK? That really surprises me.

[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 7

rbarryyoung (11/28/2008)
Really? "One hundred and One" is acceptable in the UK? That really surprises me.

Yep. Even the sample you posted above makes perfect sense to us:

nine hundred and seventy seven thousand, six hundred and ninety two pounds

About the price of a small flat in central London. Well, yesterday 😉

^{“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 8

Oh it makes sense here, colloquially it's used all the time, it just isn't acceptable in financial English and text (the usual place for numbers-to-English transformations).

[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 9

rbarryyoung (11/28/2008)
Oh it makes sense here, colloquially it's used all the time, it just isn't acceptable in financial English and text (the usual place for numbers-to-English transformations).

A quick Google returned this numbers-to-text 101 which is actually quite funny

http://74.125.77.132/search?q=cache:7U2LX8JjJAsJ:www.qualitytime-esl.com/IMG/doc/QT-ESL_15_Numbers.doc+%22six+hundred+and%22+pounds&hl=en&ct=clnk&cd=10&gl=uk

🙂

^{“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

Jeff Moden SSC Guru Points: 1004534 More actions · Answer 10

Chris Morris (11/28/2008)
rbarryyoung (11/27/2008)
Well I did test them on my laptop. Chris's ran in 13 & 15 seconds, while Jeff's ran in 5.1 and 5.2 seconds.
Testing in my reporting environment yields very similar results:
I like the new table structure, Jeff. Makes a lot more sense.

Heh... how strange that some machines should vary so greatly on the same task.

Chris's took 11 and 13 on mine and, on mine, took 7 flat on both.

Heh... guess ya gotta buy one of those fancy CPU's labelled "string compatible". 😛

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Heh. Interesting, they are using American "Billions", but still using the "and" improperly for American English.

The thing is that over here, "two hundred and twenty-three thousand dollars" legally can mean {$200.00 + $23,000.00} and at best is ambiguous.

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Jeff Moden (11/28/2008)

Chris Morris (11/28/2008)
Heh... guess ya gotta buy one of those fancy CPU's labelled "string compatible". 😛

Actually, when I got this Laptop, the marketing brochure said that the CPU was "Moden-compatible". And here, I thought that they had just misspelled "Modem". 😀

[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: 1004534 More actions · Answer 13

Heh... that's "Mo den e'nuff outa yous". 😛

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Heh. Now that sounds like the ancient art of Wi-Yi-Yotta.

😛

[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]

Mike C SSC-Insane Points: 23224 More actions · Answer 15

TheSQLGuru (10/14/2008)
That site has a lot of really great stuff. Aaron Bertrand, IIRC.
And I too think this is a presentation layer item. Or perhaps a CLR function??

Actually something like this (precalculated and stored in a dimension table) might be useful for specialized BI applications. Of course when you get up into the billions and trillions, that's a whole lot of data to store 🙂