NULL in dimensions: num to letter

Hi all,
I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
any help is very appreciated.
Regards
joujThis is a multi-part message in MIME format.
--030202070805000708070300
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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* 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
>
>
--030202070805000708070300
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
jouj wrote:
<blockquote cite="mid49A4EDB6-D88E-47A2-80DA-B135627DB81B@.microsoft.com"
type="cite">
<pre wrap="">Hi all,
I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
any help is very appreciated.
Regards
jouj
</pre>
</blockquote>
</body>
</html>
--030202070805000708070300--|||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* 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
> >
> >
> >
>|||http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
--
David Portas
SQL Server MVP
--|||This is a multi-part message in MIME format.
--020404080608020406020302
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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.
>
--020404080608020406020302
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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). 
</tt>
<blockquote><tt>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 
</tt></blockquote>
<tt>As you can see it's much uglier than 
</tt>
<blockquote><tt>select a.num, b.word from MyTable a 
Â Â Â inner join NumWords as b on a.num = b.num 
</tt></blockquote>
<tt>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?</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
jouj wrote:
<blockquote cite="midB9DC52BC-1586-4853-8C49-426B2AAF5E08@.microsoft.com"
type="cite">
<pre wrap="">Can I get the UDF and work on the performance?
I will use carefully.
</pre>
</blockquote>
</body>
</html>
--020404080608020406020302--|||This is a multi-part message in MIME format.
--010506010601040406050901
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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/view.aspx?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
>
>
--010506010601040406050901
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.solidqualitylearning.com/view.aspx?source=S_BG</a>),">http://www.solidqualitylearning.com/view.aspx?source=S_BG">http://www.solidqualitylearning.com/view.aspx?source=S_BG</a>), 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...> 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
David Portas wrote:
<blockquote cite="mid96udndwV_op1lQHfRVn-ow@.giganews.com" type="cite">
<pre wrap=""><a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.users.drew.edu/skass/sql/NameMoney.sql.txt</a>">http://www.users.drew.edu/skass/sql/NameMoney.sql.txt">http://www.users.drew.edu/skass/sql/NameMoney.sql.txt</a>
</pre>
</blockquote>
</body>
</html>
--010506010601040406050901--|||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|||This is a multi-part message in MIME format.
--070701040504070200020401
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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
>
>
--070701040504070200020401
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
jouj wrote:
<blockquote cite="mid6FD5ADE9-B94B-4BE8-AE25-0BE66D2A6369@.microsoft.com"
type="cite">
<pre wrap="">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
</pre>
</blockquote>
</body>
</html>
--070701040504070200020401--

Monday, March 12, 2012

num to letter

No comments:

Post a Comment

NULL in dimensions

Blog Archive

About Me