Re: Looking for a function - Mailing list pgsql-general

Sorry , here is the other function you are missing

/*
LICENSE: Free
DESCTIPTION: Convert a numeric amount into words.
USAGE: select amount_to_words(12345.12);
URL : http://www.pgexplorer.com
GUI Tool for Postgres
*/

CREATE FUNCTION teens_to_words(int4) RETURNS varchar(20) AS '
DECLARE
    result_str varchar(20);

BEGIN
SELECT INTO result_str
  CASE $1
    WHEN 0 THEN ''''
    WHEN 1 THEN '' TEN''
    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''
    WHEN 10 THEN '' HUNDRED''
END;

RETURN result_str;
END;
'
 LANGUAGE 'plpgsql';
CREATE FUNCTION units_to_words(int4) RETURNS varchar(20) AS '
DECLARE
    result_str varchar(20);

BEGIN
SELECT INTO result_str
  CASE $1
    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''
    WHEN 10 THEN '' TEN''
    WHEN 11 THEN '' ELEVEN''
    WHEN 12 THEN '' TWELVE''
    WHEN 13 THEN '' THIRTEEN''
    WHEN 14 THEN '' FOURTEEN''
    WHEN 15 THEN '' FIFTEEN''
    WHEN 16 THEN '' SIXTEEN''
    WHEN 17 THEN '' SEVENTEEN''
    WHEN 18 THEN '' EIGHTEEN''
    WHEN 19 THEN '' NINETEEN''
END;

RETURN result_str;
END;
'
 LANGUAGE 'plpgsql';

CREATE FUNCTION amount_to_words(numeric(15,2)) RETURNS varchar(255) AS '
DECLARE
       amount ALIAS FOR $1;
       amount_str varchar(30);
       tri int4;
       bi int4;
       mi int4;
       th int4;
       hu int4;
       de int4;
       return_str varchar(255);
BEGIN
amount_str = to_char(amount, ''000000000000D00'');
tri = substr(amount_str, 0, 3)::int4;
bi = substr(amount_str, 2, 3)::int4;
mi = substr(amount_str, 5, 3)::int4;
th = substr(amount_str, 8, 3)::int4;
hu = substr(amount_str, 11, 3)::int4;
de = substr(amount_str, 15, 2)::int4;

if tri > 0 then
       return_str = num_to_words(tri) || '' TRILLION '';
       else
       return_str = '''';
END IF;

if bi > 0 then
       return_str = return_str || num_to_words(bi) || '' BILLION '';
END IF;
if mi > 0 then
       return_str = return_str || num_to_words(mi) || '' MILLION '';
END IF;
if th > 0 then
       return_str = return_str || num_to_words(th) || '' THOUSAND '';
END IF;
if hu > 0 then
   return_str =  return_str || num_to_words(hu);
END IF;
IF return_str != '''' then
   return_str = return_str ||'' DOLLARS AND '';
   else
       return_str = ''ZERO DOLLARS AND '';
END IF;

if de > 0 then
   return_str = return_str || num_to_words(de);
END IF;
return_str = return_str || '' CENTS'';

RETURN return_str;
END;
'
 LANGUAGE 'plpgsql';





----- Original Message -----
From: "Rich Shepard" <rshepard@appl-ecosys.com>
To: <pgsql-general@postgresql.org>
Sent: Sunday, February 17, 2002 6:52 AM
Subject: [GENERAL] Looking for a function


>   Is there a function that translates a numeric dollar amount into words
for
> printing on a check? Long ago and far away, I had such a routine for
> Paradox/DOS so I wonder if such a utility (preferably in C) exits for use
> with a postgres app.
>
> Thanks,
>
> Rich
>
> Dr. Richard B. Shepard, President
>
>                        Applied Ecosystem Services, Inc. (TM)
>             2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
>  + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) |
rshepard@appl-ecosys.com
>                          http://www.appl-ecosys.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster