Number into words convertion in PostgreSQL

I was playing with some unit tests for PostgresDAC recently. And one test case was to check TPSQLDataset.Locate routine which allows partial-string and case-sensitive matching options. So I want to have some test result set with integer and text columns. The simplest for me are numbers in both representations: using digits and using words, e.g.

  1. one
  2. two
  3. three
  4. four
  5. etc.

Since I hadn’t much spare time I used nice VALUES command:

VALUES (1, 'one'), (2, 'two'), (3, 'three');

Considering I needed special names for columns I used more complex query:

SELECT col1, col2::varchar(10) FROM 
(VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t(col1, col2);

I did my task well. However I was wondering is there any built-in function in PostgreSQL which may convert integer to such words representation. The answer is: not exactly.

I found some references to cash_words function. But there is no any information (except entry in 7.3 release notes) in the official documents. The use case is quite simple:

SELECT cash_words(gen.i::money) FROM generate_series(1, 10) AS gen(i);
cash_words text

"One dollar and zero cents" "Two dollars and zero cents" "Three dollars and zero cents" "Four dollars and zero cents" "Five dollars and zero cents" "Six dollars and zero cents" "Seven dollars and zero cents" "Eight dollars and zero cents" "Nine dollars and zero cents" "Ten dollars and zero cents"

I’m fine with this result set, who needs only numbers may trim values using standard functions.

PS In case someone wants to create his own implementation using C (or plpgsql, who knows), cash_words function may be found in the \src\backend\utils\adt\cash.c file. It uses num_word private routine.

Advertisements

PostgresDAC 2.9.0 with RAD Studio XE3 support is out!

This release introduces support for RAD Studio XE3 and PostgreSQL 9.2! Several memory leaks are eliminated.

Full changelog:
[!] v9.2.0 client libraries added
[!] v9.2.0 dump & restore libraries (pg_dump.dll, pg_restore.dll) added
[+] New field data processing methods introduced for Delphi XE3
[+] Support for IProviderSupportNG interface introduced in RAD Studio XE3 added
[*] TParam.DataType property for function argument with domain type is set to base type of domain instead of ftString
[-] “E2015 Ambiguity between TDataSet::PSExecuteStatement and TPSQLDataset::PSExecuteStatement in C++ Builder XE3” bug fixed
[-] “ftDate and ftTime fields may produce memory corruption in Delphi XE3” bug fixed
[-] “TPSQLDataset.Locate method fails for timestamp fields”bug fixed
[-] Internal memory leaks of EPSQLException objects eliminated
[-] Memory leaks in TPSQLUpdateSQL’s BLOBs processing are eliminated

You’re welcome to download the PostgresDAC v2.9.0 right now at:
http://microolap.com/products/connectivity/postgresdac/download/
or login to your private area on our site at
http://microolap.com/my/downloads/