CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Pramod Edathil
Pramod Edathil

Reputation: 17

Conversion of int values to numerals in Postgresql?

I would like to know if there is a built-in way to convert integer values into numerals in PostgreSQL?

As an example, is it possible to convert the integer 10 into the string TEN.

Thank You.

Upvotes: 1

Views: 724

Answers (2)

atiruz
atiruz

Reputation: 2858

Try this query:

SELECT split_part (cash_words (10::VARCHAR::MONEY), 'dollar', 1);

It's a internal function of PostgreSQL.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324455

There's nothing built-in. For this sort of thing your best bet will be to make use of PostgreSQL's pluggable procedural languages. Use PL/Perl or PL/Python with a suitable Perl or Python library to do the job.

In this case I'd probably use PL/Perl with Lingua::EN::Numbers.

CREATE OR REPLACE FUNCTION num2en(numeric) RETURNS text AS $$
use Lingua::EN::Numbers qw(num2en);
return num2en($_[0]);
$$ LANGUAGE plperlu;

You'll need to install Lingua::EN::Numbers into the Perl being used by PostgreSQL using CPAN or system packages first. In my case (Fedora 19) this was a simple yum install perl-Lingua-EN-Numbers.noarch, then I could:

regress=> SELECT num2en(10);
 num2en 
--------
 ten
(1 row)

regress=# SELECT num2en(NUMERIC '142.5');
                num2en                
--------------------------------------
 one hundred and forty-two point five
(1 row)

By default the function is accessible by normal users so you don't have to issue any extra GRANTs.

Upvotes: 4

Related Questions