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.
- one
- two
- three
- four
- 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 |
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.
If you are looking for a more complete example of numbers to words, have a look at the snippets section on the wiki, there is an example of several ways to do this. The examples converts numeric to spanish text http://wiki.postgresql.org/wiki/Numeros_a_letras
LikeLike
Cool Darcy! Thanks!
LikeLike
You’re a great help mate! Thank you…
LikeLike