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.
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($_); $$ 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.
Try this query:
SELECT split_part (cash_words (10::VARCHAR::MONEY), 'dollar', 1);
It's a internal function of PostgreSQL.