num to letter

Hi all,
I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
any help is very appreciated.
Regards
joujjouj,
You'd probably be better off with an auxiliary table that has the
mappings of number to words since a UDF will either have to spell out
the values to a great degree (a huge case statement) or implement some
fairly complicated string parsing logic, which would slow down any
queries that used your UDF quite considerably.
Consider a table like:
Num Word
-- --
1 One
2 Two
3 Three
etc., etc.
and then just join that table into your queries with a straight-forward
inner join. That would be infinitely more efficient.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Hi all,
>I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
>any help is very appreciated.
>Regards
>jouj
>
>|||Can I get the UDF and work on the performance?
I will use carefully.
--
--
G.Haddad
--
"Mike Hodgson" wrote:

> jouj,
> You'd probably be better off with an auxiliary table that has the
> mappings of number to words since a UDF will either have to spell out
> the values to a great degree (a huge case statement) or implement some
> fairly complicated string parsing logic, which would slow down any
> queries that used your UDF quite considerably.
> Consider a table like:
> Num Word
> -- --
> 1 One
> 2 Two
> 3 Three
> etc., etc.
> and then just join that table into your queries with a straight-forward
> inner join. That would be infinitely more efficient.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> jouj wrote:
>
>|||http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
David Portas
SQL Server MVP
--|||Why are you so keen on writing a UDF? They have their place (a very
small, limited place) in SQL solutions but SQL is a set-based language
and UDFs essentially require row-by-row processing. If you tell us the
problem you're trying to solve we may be able to come up with a better
solution than a UDF (except if the problem is some university tutorial
question that says "write a UDF to convert numbers to their character
representations", in which case shame on you).
If you absolutely *must* have a UDF, here's one I just knocked up
quickly. You'd have to tart it up a little if you wanted niceties like
"and"s or commas (e.g. six thousand, four hundred and fifty two). It
assumes we're talking in base 10 and you have to limit it (I limited it
to 1-999 but it could be very easily extended just by changing the limit
and a bit of cut & paste of the "hundreds" section - obvious).
create function dbo.NumToString (@.n int)
returns varchar(100) as
begin
-- Value to return
declare @.s varchar(100)
-- Set some boundaries
if (@.n < 1 or @.n > 999)
set @.s = null
else
begin
set @.s = ''
-- Calculate the hundreds
if (@.n > 99)
begin
select @.s =
case ((@.n/100) % 10)
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end + ' hundred '
end
-- Calculate the tens
if (@.n > 9)
begin
select @.s = @.s +
case ((@.n/10) % 10)
when 0 then ''
when 1 then
case (@.n % 10)
when 0 then 'ten'
when 1 then 'eleven'
when 2 then 'twelve'
when 3 then 'thirteen'
when 4 then 'fourteen'
when 5 then 'fifteen'
when 6 then 'sixteen'
when 7 then 'seventeen'
when 8 then 'eighteen'
when 9 then 'nineteen'
end
when 2 then 'twenty'
when 3 then 'thirty'
when 4 then 'forty'
when 5 then 'fifty'
when 6 then 'sixty'
when 7 then 'seventy'
when 8 then 'eighty'
when 9 then 'ninety'
end + ' '
end
-- Calculate the single digits
if (((@.n/10) % 10) != 1)
begin
select @.s = @.s +
case (@.n % 10)
when 0 then ''
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end
end
end
return (@.s)
end
go
As you can see it's much uglier than
select a.num, b.word from MyTable a
inner join NumWords as b on a.num = b.num
and the performance of it would be dreadful compared to this select
query (assuming you had the NumWords.num column indexed).
I really have to ask again - why does it have to be a UDF?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Can I get the UDF and work on the performance?
>I will use carefully.
>|||Is that from Steve Kass? Even that is fairly ugly (no offense Steve).
Better than my 10 minute effort but ugly all the same. I guess I could
have put it in a loop based on the calculated magnitude, concatenating
the magnitude with the digit each iteration of the loop thereby making
the code a little shorter but it still would have been yuk (to use the
technical term). I don't understand why so many people want to use UDFs
to solve their problems; IMHO that's almost always the wrong approach.
Itzik Ben-Gan, of SolidQualityLearning fame
(http://www.solidqualitylearning.com...spx?source=S_BG), is a bit of
a fan of auxiliary tables and I think in this example that would be perfect.
<Mike hops down off his soapbox...>
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Portas wrote:

>http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
>
>|||Dear Mike,
in my application, I need sometimes to convert a number > 1,000,000,000.00.
so is it optimal to save this number of records in a single table?
That's why I needed the UDF.
Thanks anyway.
Regards.
jouj|||Admittedly it would make for a rather large table (eg. if you assume the
table has an int & a varchar that averages about 50 bytes per row (just
a guess), for a billion rows you talking about 50G). However, if you
put a clustered index on the int column, the query would only need to do
an index seek to get the relevant string, which would mean somewhere
between 3 - 5 logical reads (ie. the number of levels needed in the
index for that many rows) and that should be lightning fast in
comparison to the UDF and no matter how many rows you add to your
auxiliary table the logical reads would still only be in the order or 4
or 5 or 6.
It would take a long time to populate the table with 1,000,000,000+ rows
but it could be done overnight (or offline) with a script similar to the
UDF code and once it's populated everything would be sweet (assuming you
had the disk space to spare).
Anyway, I understand where you're coming from a bit better now.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Dear Mike,
>in my application, I need sometimes to convert a number > 1,000,000,000.00.
>so is it optimal to save this number of records in a single table?
>That's why I needed the UDF.
>Thanks anyway.
>Regards.
>jouj
>
>