CODEWITHSUNDEEP
sqlpostgresqlnumber-formatting
Joe DiNottra
Joe DiNottra

Reputation: 1008

Can a format value "divide" a number in PostgreSQL?

One of the columns of my table has values that can typically range from 3500 to 8 million. Is it possible to specify a format that can divide the number when formatting?

For example, I have the following values:

3500
81000
1678500

Ideally I would like a format value (coming from another config table) that would format the numbers in "thousands" with 1 decimal place:

3.5
81.0
1678.5

But this format value could also be different for other cases, so they could be formatted in millions with two decimal places:

0.00
0.08
1.68

Is this possible, or do I need to divide the numbers myself before applying the formatting?

Upvotes: 0

Views: 278

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Is this possible, or do I need to divide the numbers myself before applying the formatting?

You need to do the division. You can use CASE WHEN, by the way, if you aren't having numeric values to track what to divide by:

SELECT tablevalue / CASE divby WHEN 'thousand' THEN 1000 WHEN 'million' THEN 1000000 ELSE 1 END

I presume you'll have some column in your "format" table that also specifies what to divide by..

So you don't want to add a column.. you can store the info in the existing column.. you just have to work more to get it out:

SELECT 
  TO_CHAR(
    somenumber / CASE RIGHT(format, 1) WHEN 'k' THEN 1000 WHEN 'M' THEN 1000000 END,
    LEFT(format, -1)
  )

So now you can make your format like 99D99k and the k will cause a divide by 1000 and the result is formatted to 99.99, so if you have 1234, format 9.99k you'll get '1.23'out of it.

If you want the [k] at the start it's just some jiggling of the LEFT and RIGHT functions..

  TO_CHAR(
    somenumber / CASE LEFT(format, 3) WHEN '[k]' THEN 1000 WHEN '[M]' THEN 1000000 END,
    RIGHT(format, -3)
  )

Upvotes: 1

Related Questions