Re: Number Conversion Function

From: justin <justin(at)emproshunts(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Tino Wildenhain <tino(at)living-examples(dot)com>, Abdul Rehman <abr_ora(at)yahoo(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Number Conversion Function
Date: 2009-04-07 13:39:41
Message-ID: 49DB579D.9070505@emproshunts.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!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">
<br>
<br>
Tom Lane wrote:
<blockquote cite="mid:27615(dot)1239027099(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Tino Wildenhain <a class="moz-txt-link-rfc2396E" href="mailto:tino(at)living-examples(dot)com">&lt;tino(at)living-examples(dot)com&gt;</a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">I would not recommend to do this within the database. Thats typical
a job for your presentation layer.
</pre>
</blockquote>
<pre wrap=""><!---->
... but having said that, I think the "money" datatype has a function
for this. Whether that's of any use to you I dunno; money is pretty
restrictive about what it can handle.

regards, tom lane
</pre>
</blockquote>
<br>
I disagree the database is the wrong place, there are cases it makes
sense.<br>
<br>
I have looked for what Tom talks about for the money type i can't find
any thing??&nbsp; so I wrote a function primary purpose is used with checks
but with a little modification will work for anyone one and has no
practical limitation .&nbsp;&nbsp; It will work to Quintillion. <br>
<br>
CREATE OR REPLACE FUNCTION spellNumericValue( pValue numeric)<br>
&nbsp; RETURNS text AS<br>
$BODY$<br>
DECLARE<br>
&nbsp; _dollar bigint = trunc(pValue)::text;<br>
&nbsp; _cents int = ((pValue - trunc(pValue))*100)::int;<br>
&nbsp; _spelledAmount text = '' ;<br>
&nbsp; _brokenOut int[] ;<br>
&nbsp; _pos integer = 0;<br>
&nbsp; _word text ;<br>
BEGIN<br>
&nbsp;&nbsp;&nbsp; <br>
&nbsp;&nbsp;&nbsp; --lets breakout the number into hundreds into a array<br>
&nbsp;&nbsp;&nbsp; WHILE _dollar &gt; 0 loop<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _brokenOut = array_append(_brokenOut, (_dollar%1000)::int);<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _dollar = trunc(_dollar/1000);<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _pos = _pos + 1;<br>
&nbsp;&nbsp;&nbsp; End Loop;<br>
<br>
&nbsp;&nbsp;&nbsp; --this works on numbers between 1 to 999 transforming into english
words. then goes to the <br>
&nbsp;&nbsp;&nbsp; --next set of numbers in the array working backwards as the array
was loaded backwards<br>
&nbsp;&nbsp;&nbsp; --Meaning the highest value is in the last element of the array
_brokenOut<br>
&nbsp;&nbsp;&nbsp; --This also assumes words thousands millions, billions... occurs
every 10^3 .<br>
&nbsp;&nbsp;&nbsp; while _pos &gt; 0 loop<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; if _brokenOut[_pos] &gt;99 then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; SELECT CASE <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 899 THEN 'Nine Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 799 THEN 'Eight Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 699 THEN 'Seven Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 599 THEN 'Six Hundred ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 499 THEN 'Five Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 399 THEN 'Four Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 299 THEN 'Three Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 199 THEN 'Two Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos] &gt; 99 THEN 'One Hundred '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; else ''<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; end<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; into _word;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || _word ;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; end if;<br>
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Select Case<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 10 THEN 'Ten '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 11 THEN 'Eleve '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 12 THEN 'Twelve '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 13 THEN 'Thirteen '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 14 THEN 'Fourteen '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 15 THEN 'Fifteen '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 16 THEN 'Sixteen '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 17 THEN 'Seventeen '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 18 THEN 'Eighteen'<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%100 = 19 THEN 'Nineteen '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=2 THEN 'Twenty ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=3 THEN 'Thirty ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=4 THEN 'Fourty ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=5 THEN 'Fifty ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=6 THEN 'Sixty ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=7 THEN 'Seventy ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=8 THEN 'Eighty ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]/10%10=9 THEN 'Ninety&nbsp; ' <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ELSE ''<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; End<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; into _word;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || _word;<br>
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; if _brokenOut[_pos]%100 &lt; 10 or _brokenOut[_pos]%100 &gt; 20
then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; SELECT CASE <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 1 THEN 'One '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 2 THEN 'Two'<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 3 THEN 'Three '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 4 THEN 'Four '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 5 THEN 'Five '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 6 THEN 'Six '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 7 THEN 'Seven '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 8 THEN 'Eight '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN _brokenOut[_pos]%10 = 9 THEN 'Nine '<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ELSE ''<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; end<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; into _word;<br>
&nbsp;&nbsp;&nbsp; <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || _word;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; end if ;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; If _pos = 2 then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || 'Thousand ';<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; elsif _pos = 3&nbsp; then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || 'Million';<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; elsif _pos = 4&nbsp; then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || 'Billion ';<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; elsif _pos = 5 then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || 'Trillion ';<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; elsif _pos = 6 then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || 'Quadrillion ';<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; elsif _pos = 7 then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || 'Quintillion ';<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; else <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || '';<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; end if;<br>
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _pos = _pos-1;<br>
&nbsp;&nbsp;&nbsp; end loop;<br>
<br>
&nbsp;&nbsp;&nbsp; if _cents = 0 then<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || ' and Zero cents';<br>
&nbsp;&nbsp;&nbsp; else<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; _spelledAmount = _spelledAmount || 'and ' || _cents::text ||
'/100 cents';<br>
&nbsp;&nbsp;&nbsp; end if ;<br>
&nbsp;&nbsp;&nbsp; return _SpelledAmount;<br>
&nbsp;&nbsp;&nbsp; <br>
END;<br>
$BODY$<br>
&nbsp; LANGUAGE 'plpgsql' VOLATILE<br>
<br>
<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 10.5 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neithan Maxcom 2009-04-07 13:52:05 I can't solve this, PostgreSQL won't install no matter what!
Previous Message Grzegorz Jaśkiewicz 2009-04-07 09:34:31 bug in 8.4 pg_dumpall ?