Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function to spell out a number

Function to spell out a number

From
lists@kambanaria.org
Date:
Hi everyone,
I've checked the manual and a bit online but could not find this:
Is there a provided function to spell out numbers in Postgres? (in
different languages if possible.)
For example:
to_words(1234) => 'two hundred and thirty four"
Kind regards:
al_shopov

Re: Function to spell out a number

From
Mladen Gogala
Date:
lists@kambanaria.org wrote:
> Hi everyone,
> I've checked the manual and a bit online but could not find this:
> Is there a provided function to spell out numbers in Postgres? (in
> different languages if possible.)
> For example:
> to_words(1234) => 'two hundred and thirty four"
> Kind regards:
> al_shopov
>
>
There is a Perl module Number::Spell which has that functionality. It
supports all important languages, both British English and the American
English. You can write a plperlu function and grant execute on it to the
application user.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Re: Function to spell out a number

From
Thomas Kellerer
Date:
lists@kambanaria.org, 04.11.2010 15:28:
> Hi everyone,
> I've checked the manual and a bit online but could not find this:
> Is there a provided function to spell out numbers in Postgres? (in
> different languages if possible.)
> For example:
> to_words(1234) =>  'two hundred and thirty four"
> Kind regards:
> al_shopov
>

For english:
http://wiki.postgresql.org/wiki/Numeric_to_English

Spanish:
http://wiki.postgresql.org/wiki/Numeros_a_letras

Re: Function to spell out a number

From
Jasen Betts
Date:
On 2010-11-04, lists@kambanaria.org <lists@kambanaria.org> wrote:
> Hi everyone,
> I've checked the manual and a bit online but could not find this:
> Is there a provided function to spell out numbers in Postgres?

yes, cash_words(), it's deprecated.

> (in different languages if possible.)

not sure about that.

> For example:
> to_words(1234) => 'two hundred and thirty four"
> Kind regards:
> al_shopov

CREATE FUNCTION to_words(num integer) RETURNS text
AS $$
    DECLARE
       str TEXT;
    BEGIN
       str=cash_words(num * ('1.0'::money));
       str=REPLACE(str,' dollars and zero cents','');
       str=REPLACE(str,' dollar and zero cents','');
    RETURN lower(str);
    END;
$$
LANGUAGE plpgsql;

the type 'money' is deprecated, and the string 'dollar'
may be locale dependant so it's probably a bad idea to
do this.  This is probably why cash_words is not
mentioned in the 8.4 manual.


http://icu-project.org/apiref/icu4j/com/ibm/icu/text/

May be a better approach, but you'll still need find/write
rulesets, and figure out how to call it from postgres.



if this is for printing cheques just listing the digits
is acceptable in my exerience

to_words(1234) => 'one two three four'

this can done using repeated (or nested) replaces.



--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁