Thread: Number Conversion Function
Hi all,
Can any body help me in converting numeric values into words via postgres function: i.e. 313 to THREE HUNDRED THREE
Regards,
Abdul Rehman.
Can any body help me in converting numeric values into words via postgres function: i.e. 313 to THREE HUNDRED THREE
Regards,
Abdul Rehman.
In response to Abdul Rehman : > Hi all, > > Can any body help me in converting numeric values into words via postgres > function: i.e. 313 to THREE HUNDRED THREE You can use the PERL-Module from http://search.cpan.org/~sburke/Lingua-EN-Numbers-1.01/lib/Lingua/EN/Numbers.pm and plperlU Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Abdul Rehman wrote: > Hi all, > > Can any body help me in converting numeric values into words via > postgres function: i.e. 313 to THREE HUNDRED THREE I would not recommend to do this within the database. Thats typical a job for your presentation layer. Regards Tino
Tino Wildenhain <tino@living-examples.com> writes:
> Abdul Rehman wrote:
>> Can any body help me in converting numeric values into words via
>> postgres function: i.e. 313 to THREE HUNDRED THREE
> I would not recommend to do this within the database. Thats typical
> a job for your presentation layer.
... 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
Abdul Rehman wrote:
> Can any body help me in converting numeric values into words
> via postgres function: i.e. 313 to THREE HUNDRED THREE
Here is a little PL/pgSQL function that should do what you
want for numbers < 1000000000000.
It should be easy to extend if you switch to bigint and decide
if you want British or American English.
I played with it, but it may still contain bugs.
Yours,
Laurenz Albe
CREATE OR REPLACE FUNCTION to_text(integer) RETURNS text LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE WHEN $1<1 THEN NULL
WHEN $1=1 THEN 'ONE'
WHEN $1=2 THEN 'TWO'
WHEN $1=3 THEN 'THREE'
WHEN $1=4 THEN 'FOUR'
WHEN $1=5 THEN 'FIVE'
WHEN $1=6 THEN 'SIX'
WHEN $1=7 THEN 'SEVEN'
WHEN $1=8 THEN 'EIGHT'
WHEN $1=9 THEN 'NINE'
WHEN $1=10 THEN 'TEN'
WHEN $1=11 THEN 'ELEVEN'
WHEN $1=12 THEN 'TWELVE'
WHEN $1=13 THEN 'THIRTEEN'
WHEN $1=14 THEN 'FOURTEEN'
WHEN $1=15 THEN 'FIFTEEN'
WHEN $1=16 THEN 'SIXTEEN'
WHEN $1=17 THEN 'SEVENTEEN'
WHEN $1=18 THEN 'EIGHTEEN'
WHEN $1=19 THEN 'NINETEEN'
WHEN $1<100 THEN CASE
WHEN $1/10=2 THEN 'TWENTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=3 THEN 'THIRTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=4 THEN 'FOURTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=5 THEN 'FIFTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=6 THEN 'SIXTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=7 THEN 'SEVENTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=8 THEN 'EIGHTY' || COALESCE(' ' || to_text($1%10), '')
WHEN $1/10=9 THEN 'NINETY' || COALESCE(' ' || to_text($1%10), '')
END
WHEN $1<1000
THEN to_text($1/100) || ' HUNDRED' ||
COALESCE(' AND ' || to_text($1%100), '')
WHEN $1<1000000
THEN to_text($1/1000) || ' THOUSAND' ||
CASE WHEN $1%1000 < 100
THEN COALESCE(' AND ' || to_text($1%1000), '')
ELSE COALESCE(' ' || to_text($1%1000), '')
END
WHEN $1<1000000000
THEN to_text($1/1000000) || ' MILLION' ||
CASE WHEN $1%1000000 < 100
THEN COALESCE(' AND ' || to_text($1%1000000), '')
ELSE COALESCE(' ' || to_text($1%1000000), '')
END
END$$;
Albe Laurenz wrote: > Abdul Rehman wrote: > > Can any body help me in converting numeric values into words > > via postgres function: i.e. 313 to THREE HUNDRED THREE > > Here is a little PL/pgSQL function that should do what you > want for numbers < 1000000000000. > It should be easy to extend if you switch to bigint and decide > if you want British or American English. This is something that would be probably useful to have in http://wiki.postgresql.org/wiki/Snippets We have a function there to generate the numbers in spanish ... -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Tom Lane wrote:
Tino Wildenhain <tino@living-examples.com> writes:
I would not recommend to do this within the database. Thats typical
a job for your presentation layer.
... 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
I disagree the database is the wrong place, there are cases it makes
sense.
I have looked for what Tom talks about for the money type i can't find
any thing?? 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 . It will work to Quintillion.
CREATE OR REPLACE FUNCTION spellNumericValue( pValue numeric)
RETURNS text AS
$BODY$
DECLARE
_dollar bigint = trunc(pValue)::text;
_cents int = ((pValue - trunc(pValue))*100)::int;
_spelledAmount text = '' ;
_brokenOut int[] ;
_pos integer = 0;
_word text ;
BEGIN
--lets breakout the number into hundreds into a array
WHILE _dollar > 0 loop
_brokenOut = array_append(_brokenOut, (_dollar%1000)::int);
_dollar = trunc(_dollar/1000);
_pos = _pos + 1;
End Loop;
--this works on numbers between 1 to 999 transforming into english
words. then goes to the
--next set of numbers in the array working backwards as the array
was loaded backwards
--Meaning the highest value is in the last element of the array
_brokenOut
--This also assumes words thousands millions, billions... occurs
every 10^3 .
while _pos > 0 loop
if _brokenOut[_pos] >99 then
SELECT CASE
WHEN _brokenOut[_pos]
>899 THEN 'Nine Hundred '
WHEN _brokenOut[_pos]
>799 THEN 'Eight Hundred '
WHEN _brokenOut[_pos]
>699 THEN 'Seven Hundred '
WHEN _brokenOut[_pos]
>599 THEN 'Six Hundred '
WHEN _brokenOut[_pos]
>499 THEN 'Five Hundred '
WHEN _brokenOut[_pos]
>399 THEN 'Four Hundred '
WHEN _brokenOut[_pos]
>299 THEN 'Three Hundred '
WHEN _brokenOut[_pos]
>199 THEN 'Two Hundred '
WHEN _brokenOut[_pos]
>99 THEN 'One Hundred '
else ''
end
into _word;
_spelledAmount = _spelledAmount || _word ;
end if;
Select Case
WHEN _brokenOut[_pos]%100 = 10 THEN 'Ten '
WHEN _brokenOut[_pos]%100 = 11 THEN 'Eleve
'
WHEN _brokenOut[_pos]%100 = 12 THEN 'Twelve
'
WHEN _brokenOut[_pos]%100 = 13 THEN
'Thirteen'
WHEN _brokenOut[_pos]%100 = 14 THEN
'Fourteen'
WHEN _brokenOut[_pos]%100 = 15 THEN
'Fifteen'
WHEN _brokenOut[_pos]%100 = 16 THEN
'Sixteen'
WHEN _brokenOut[_pos]%100 = 17 THEN
'Seventeen'
WHEN _brokenOut[_pos]%100 = 18 THEN
'Eighteen'
WHEN _brokenOut[_pos]%100 = 19 THEN
'Nineteen'
WHEN _brokenOut[_pos]/10%10=2 THEN 'Twenty
'
WHEN _brokenOut[_pos]/10%10=3 THEN 'Thirty
'
WHEN _brokenOut[_pos]/10%10=4 THEN 'Fourty
'
WHEN _brokenOut[_pos]/10%10=5 THEN 'Fifty '
WHEN _brokenOut[_pos]/10%10=6 THEN 'Sixty '
WHEN _brokenOut[_pos]/10%10=7 THEN 'Seventy
'
WHEN _brokenOut[_pos]/10%10=8 THEN 'Eighty
'
WHEN _brokenOut[_pos]/10%10=9 THEN
'Ninety '
ELSE ''
End
into _word;
_spelledAmount = _spelledAmount || _word;
if _brokenOut[_pos]%100 < 10 or _brokenOut[_pos]%100 > 20
then
SELECT CASE
WHEN _brokenOut[_pos]%10
=1 THEN 'One '
WHEN _brokenOut[_pos]%10
=2 THEN 'Two'
WHEN _brokenOut[_pos]%10
=3 THEN 'Three '
WHEN _brokenOut[_pos]%10
=4 THEN 'Four '
WHEN _brokenOut[_pos]%10
=5 THEN 'Five '
WHEN _brokenOut[_pos]%10
=6 THEN 'Six '
WHEN _brokenOut[_pos]%10
=7 THEN 'Seven '
WHEN _brokenOut[_pos]%10
=8 THEN 'Eight '
WHEN _brokenOut[_pos]%10
=9 THEN 'Nine '
ELSE ''
end
into _word;
_spelledAmount = _spelledAmount || _word;
end if ;
If _pos = 2 then
_spelledAmount = _spelledAmount || 'Thousand ';
elsif _pos = 3 then
_spelledAmount = _spelledAmount || 'Million';
elsif _pos = 4 then
_spelledAmount = _spelledAmount || 'Billion ';
elsif _pos = 5 then
_spelledAmount = _spelledAmount || 'Trillion ';
elsif _pos = 6 then
_spelledAmount = _spelledAmount || 'Quadrillion ';
elsif _pos = 7 then
_spelledAmount = _spelledAmount || 'Quintillion ';
else
_spelledAmount = _spelledAmount || '';
end if;
_pos = _pos-1;
end loop;
if _cents = 0 then
_spelledAmount = _spelledAmount || ' and Zero cents';
else
_spelledAmount = _spelledAmount || 'and ' || _cents::text ||
'/100 cents';
end if ;
return _SpelledAmount;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
justin wrote: > > > Tom Lane wrote: >> Tino Wildenhain <tino@living-examples.com> writes: >> >>> I would not recommend to do this within the database. Thats typical >>> a job for your presentation layer. >>> >> >> ... 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 >> > > I disagree the database is the wrong place, there are cases it makes sense. > Which cases would that be? Regards Tino
Tino Wildenhain wrote: > justin wrote: >> >> I disagree the database is the wrong place, there are cases it makes >> sense. >> > Which cases would that be? > > Regards > Tino > Report engines that don't have this ability . I use for check writing. To come think, I don't know of UI framework or report engine that has this ability.
On Mon, Apr 13, 2009 at 2:40 PM, Tino Wildenhain <tino@wildenhain.de> wrote: > justin wrote: >> >> >> Tom Lane wrote: >>> >>> Tino Wildenhain <tino@living-examples.com> writes: >>> >>>> >>>> I would not recommend to do this within the database. Thats typical >>>> a job for your presentation layer. >>>> >>> >>> ... 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 >>> >> >> I disagree the database is the wrong place, there are cases it makes >> sense. >> > Which cases would that be? What if you had several different 'presentation layers' interfacing with the database? Say, a internal web app, some reporting programs used by sales, dept, the old delphi program that no one ever bothered to rewrite, etc. Do you really want to implement the formatting function in all those places? as a general rule, the more i do in the database, the easier things are for me in the long run. merlin