Thread: How to convert numbers into words in postgresql
i want to convert numbers into words in postgresql.is there any query for it? if yes please reply soon -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-convert-numbers-into-words-in-postgresql-tp5755370.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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
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
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
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
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
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
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
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
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
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.
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
-----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-----
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
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.