Number Conversion Function

Started by Abdul Rahmanabout 17 years ago10 messagesgeneral

Jump to latest

Abdul Rahman

abr_ora@yahoo.com

about 17 years ago

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.

Tino Wildenhain

tino@living-examples.com

about 17 years ago

In reply to: Abdul Rahman (#1)

Re: Number Conversion Function

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

A. Kretschmer

andreas.kretschmer@schollglas.com

about 17 years ago

In reply to: Abdul Rahman (#1)

Re: Number Conversion Function

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

Tom Lane

tgl@sss.pgh.pa.us

about 17 years ago

In reply to: Tino Wildenhain (#2)

Re: Number Conversion Function

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

Laurenz Albe

laurenz.albe@cybertec.at

about 17 years ago

In reply to: Abdul Rahman (#1)

Re: Number Conversion Function

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$$;

Alvaro Herrera

alvherre@2ndquadrant.com

about 17 years ago

In reply to: Laurenz Albe (#5)

Re: Number Conversion Function

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

Justin

justin@emproshunts.com

about 17 years ago

In reply to: Tom Lane (#4)

Re: Number Conversion Function

<!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">
 
 
Tom Lane wrote:
<blockquote cite="mid:27615.1239027099@sss.pgh.pa.us" type="cite">
<pre wrap="">Tino Wildenhain <a class="moz-txt-link-rfc2396E" href="mailto:tino@living-examples.com"><tino@living-examples.com></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>
 
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 
 
 
</body>
</html>

Tino Wildenhain

tino@wildenhain.de

almost 17 years ago

In reply to: Justin (#7)

Re: Number Conversion Function

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

Justin

justin@emproshunts.com

almost 17 years ago

In reply to: Tino Wildenhain (#8)

Re: Number Conversion Function

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.

#10

Merlin Moncure

mmoncure@gmail.com

almost 17 years ago

In reply to: Tino Wildenhain (#8)

Re: Number Conversion Function

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