Thread: stored proc - how to format numbers?

In PostgreSQL (9.x), I have a variable that I want to format with commas.

I do not know how many digits will be in the number, it could be an int
(2Billion+) or a BigInt (18 digits or more).

All I simply want to do is to use:  to_number( and have the commas put
in before display.

There are no example of this in the documentation that I could find.

thanks

J.C.

On 21/09/2011 19:36, J.V. wrote:
> In PostgreSQL (9.x), I have a variable that I want to format with commas.
>
> I do not know how many digits will be in the number, it could be an int
> (2Billion+) or a BigInt (18 digits or more).
>
> All I simply want to do is to use:  to_number( and have the commas put
> in before display.

You're looking for to_char():

  http://www.postgresql.org/docs/9.1/static/functions-formatting.html

Something like this ought to do what you want:

  select to_char(12345678, 'FM999,999,990');

Each 9 in the formatting pattern will output a digit only if there's a
corresponding one in the number; and the FM trims off any padding
spaces. Just make sure you have enough 9s in the pattern for any
possible number you'll encounter.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie