Search code examples
sqlnumber-formatting

How to format a number in thousands in SQL

I'm trying to format a number like 238350.50 into "238" with SQL. I round to the nearest thousend using ROUND(number_x, -3) and it works fine but I can't get rid of the hundreds digits. A few more examples of how the formatting should work:

  • 499.99 becomes 0
  • 500 becomes 1
  • 1250 becomes 1
  • 22500 becomes 23
  • 231600 becomes 232
  • etc.

You can think of it like the "k" formatting f.e. 10.000 becomes "10k".

Thanks for the help!

Solution

  • Round it by the 1000, divide by the 1000

    ROUND(number_x, -3)/1000

    with cte as (
select 499.99 as number_x from dual
union all select 500 from dual
union all select 1250 from dual
union all select 22500 from dual
union all select 231600 from dual
)
select number_x, ROUND(number_x, -3)/1000 as new_number_x
from CTE
    NUMBER_X NEW_NUMBER_X
    499.99 0
    500 1
    1250 1
    22500 23
    231600 232

    Or divide first, then round it.

    ROUND(number_x/1000)