How to convert numbers into words in postgresql

On 14/05/2013 07:27, Jashaswee wrote:

i want to convert numbers into words in postgresql.is there any query
for it? if yes please reply soon

What exactly are you trying to do?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/13/2013 11:27 PM, Jashaswee wrote:

i want to convert numbers into words in postgresql.is there any query for it?
if yes please reply soon

you mean, like 123 -> "one hundred twenty three" ? that would be
better done in your client software.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jashaswee escribió:

i want to convert numbers into words in postgresql.is there any query for it?
if yes please reply soon

Try cash_words()

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/14/2013 2:57 PM, Alvaro Herrera wrote:

Jashaswee escribió:

i want to convert numbers into words in postgresql.is there any query for it?
if yes please reply soon

Try cash_words()

um, where is that documented? I've searched the various 'functions'
pages, and not seen it, nor is it in the manual Index.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, May 14, 2013 at 4:32 PM, John R Pierce <pierce@hogranch.com> wrote:

On 5/13/2013 11:27 PM, Jashaswee wrote:

i want to convert numbers into words in postgresql.is there any query for
it?
if yes please reply soon

you mean, like 123 -> "one hundred twenty three" ? that would be better
done in your client software.

idk: it only took a few minutes to convert this routine:

http://www.sqlusa.com/bestpractices2008/number-to-words/

CREATE OR REPLACE FUNCTION fnNumberToWords(n BIGINT) RETURNS TEXT AS
$$
DECLARE
e TEXT;
BEGIN

WITH Below20(Word, Id) AS
(
VALUES
('Zero', 0), ('One', 1),( 'Two', 2 ), ( 'Three', 3),
( 'Four', 4 ), ( 'Five', 5 ), ( 'Six', 6 ), ( 'Seven', 7 ),
( 'Eight', 8), ( 'Nine', 9), ( 'Ten', 10), ( 'Eleven', 11 ),
( 'Twelve', 12 ), ( 'Thirteen', 13 ), ( 'Fourteen', 14),
( 'Fifteen', 15 ), ('Sixteen', 16 ), ( 'Seventeen', 17),
('Eighteen', 18 ), ( 'Nineteen', 19 )
),
Below100(Word, Id) AS
(
VALUES
('Twenty', 2), ('Thirty', 3),('Forty', 4), ('Fifty', 5),
('Sixty', 6), ('Seventy', 7), ('Eighty', 8), ('Ninety', 9)
)
SELECT
CASE
WHEN n = 0 THEN ''
WHEN n BETWEEN 1 AND 19
THEN (SELECT Word FROM Below20 WHERE ID=n)
WHEN n BETWEEN 20 AND 99
THEN (SELECT Word FROM Below100 WHERE ID=n/10) || '-' ||
fnNumberToWords( n % 10)
WHEN n BETWEEN 100 AND 999
THEN (fnNumberToWords( n / 100)) || ' Hundred ' ||
fnNumberToWords( n % 100)
WHEN n BETWEEN 1000 AND 999999
THEN (fnNumberToWords( n / 1000)) || ' Thousand ' ||
fnNumberToWords( n % 1000)
WHEN n BETWEEN 1000000 AND 999999999
THEN (fnNumberToWords( n / 1000000)) || ' Million ' ||
fnNumberToWords( n % 1000000)
WHEN n BETWEEN 1000000000 AND 999999999999
THEN (fnNumberToWords( n / 1000000000)) || ' Billion ' ||
fnNumberToWords( n % 1000000000)
WHEN n BETWEEN 1000000000000 AND 999999999999999
THEN (fnNumberToWords( n / 1000000000000)) || ' Trillion ' ||
fnNumberToWords( n % 1000000000000)
WHEN n BETWEEN 1000000000000000 AND 999999999999999999
THEN (fnNumberToWords( n / 1000000000000000)) || ' Quadrillion ' ||
fnNumberToWords( n % 1000000000000000)
WHEN n BETWEEN 1000000000000000000 AND 999999999999999999999
THEN (fnNumberToWords( n / 1000000000000000000)) || ' Quintillion ' ||
fnNumberToWords( n % 1000000000000000000)
ELSE ' INVALID INPUT' END INTO e;

e := RTRIM(e);

IF RIGHT(e,1)='-' THEN
e := RTRIM(LEFT(e,length(e)-1));
END IF;

RETURN e;
END;
$$ LANGUAGE PLPGSQL;

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/14/2013 03:17 PM, John R Pierce wrote:

On 5/14/2013 2:57 PM, Alvaro Herrera wrote:

Jashaswee escribi�:

i want to convert numbers into words in postgresql.is there any

query for it?

if yes please reply soon

Try cash_words()

um, where is that documented? I've searched the various 'functions'
pages, and not seen it, nor is it in the manual Index.

production=> \df cash_words
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+--------
pg_catalog | cash_words | text | money | normal

Seems to only work with money type.

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, May 14, 2013 at 5:24 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 05/14/2013 03:17 PM, John R Pierce wrote:

On 5/14/2013 2:57 PM, Alvaro Herrera wrote:

Jashaswee escribió:

i want to convert numbers into words in postgresql.is there any

query for it?

if yes please reply soon

Try cash_words()

um, where is that documented? I've searched the various 'functions'
pages, and not seen it, nor is it in the manual Index.

production=> \df cash_words
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+--------
pg_catalog | cash_words | text | money | normal

Seems to only work with money type.

from the source:
/* cash_words()
* This converts a int4 as well but to a representation using words
* Obviously way North American centric - sorry
*/
Datum
cash_words(PG_FUNCTION_ARGS)
{

so, it comes down to the money type is one of the more bizarre things
left from postgres past and we try not to advertise too loudly I
suppose.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/14/2013 3:24 PM, Adrian Klaver wrote:

um, where is that documented? I've searched the various 'functions'
pages, and not seen it, nor is it in the manual Index.

production=> \df cash_words
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+--------

pg_catalog | cash_words | text | money | normal

Seems to only work with money type.

not sure I'd call that documentation. I was asking, where is that
described in the postgresql manual? I looked in every category I
thought was appropriate here,
http://www.postgresql.org/docs/current/static/functions.html and didn't
find it.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John R Pierce wrote:

not sure I'd call that documentation. I was asking, where is that
described in the postgresql manual? I looked in every category I
thought was appropriate here,
http://www.postgresql.org/docs/current/static/functions.html and
didn't find it.

A quick grep in doc/src/sgml/ says that cash_words only appears once:

./release-old.sgml:<listitem><para>Fix cash_words() to not overflow buffer (Tom)</para></listitem>

This is a release note item for the 7.3 release.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 15/05/13 08:27, Merlin Moncure wrote:

On Tue, May 14, 2013 at 5:24 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 05/14/2013 03:17 PM, John R Pierce wrote:

On 5/14/2013 2:57 PM, Alvaro Herrera wrote:

Jashaswee escribi�:

i want to convert numbers into words in postgresql.is there any

query for it?

if yes please reply soon

Try cash_words()

um, where is that documented? I've searched the various 'functions'
pages, and not seen it, nor is it in the manual Index.

production=> \df cash_words
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------------+------------------+---------------------+--------
pg_catalog | cash_words | text | money | normal

Seems to only work with money type.

from the source:
/* cash_words()
* This converts a int4 as well but to a representation using words
* Obviously way North American centric - sorry
*/
Datum
cash_words(PG_FUNCTION_ARGS)
{

so, it comes down to the money type is one of the more bizarre things
left from postgres past and we try not to advertise too loudly I
suppose.

merlin

What the?
I currently use a python number to words module (n2w.py if OP is
interested in it, let me know). I've been using this for years and never
knew about the cash_words() function, what other secret functions does
postgres have? :)
Now there is a use for the money type, perhaps this function could be
upgrade to not need it?

jules.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/14/2013 03:27 PM, Merlin Moncure wrote:

On Tue, May 14, 2013 at 5:24 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

Seems to only work with money type.

from the source:
/* cash_words()
* This converts a int4 as well but to a representation using words
* Obviously way North American centric - sorry
*/
Datum
cash_words(PG_FUNCTION_ARGS)
{

Well Pg 9.0 did not get that memo:)

test=> SELECT version();
version

----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.13 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.7.1 20120723 [gcc-4_7-branch revision 189773], 32-bit
(1 row)

test=> SELECT cash_words(123);
ERROR: function cash_words(integer) does not exist
LINE 1: SELECT cash_words(123);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.

Though using text works:

test=> SELECT cash_words('123');
cash_words
-------------------------------------------------
One hundred twenty three dollars and zero cents

so, it comes down to the money type is one of the more bizarre things
left from postgres past and we try not to advertise too loudly I
suppose.

Agreed.

merlin

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Jashaswee asked:

i want to convert numbers into words in postgresql.
is there any query for it?

Easy enough with PlPerl:

$ sudo apt-get install liblingua-en-inflect-perl

$ createlang plperlu

$ psql <<eot

create or replace function numwords(int)
returns text
language plperlu
immutable
as '
use Lingua::EN::Inflect qw( NUMWORDS );
return NUMWORDS(shift);
';
eot

CREATE FUNCTION

$ psql -tc 'select numwords(1234)'

one thousand, two hundred and thirty-four

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201305150015
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlGTDFwACgkQvJuQZxSWSsgpIACgyXX3Bt3SMDje/5V+tzSMESD+
HdsAmwZpYqWgnZeZvmEn8jclUCQzdKTG
=x9DW
-----END PGP SIGNATURE-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jashaswee wrote:

i want to convert numbers into words in postgresql.is there any query for it?
if yes please reply soon

I found two solutions in the Wiki:
http://wiki.postgresql.org/wiki/Integer_to_Text
http://wiki.postgresql.org/wiki/Numeric_to_English

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

thnx for your help albe.
i went through this code its working fine .

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-convert-numbers-into-words-in-postgresql-tp5755370p5755558.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general