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!

CodePudding user response：

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

Or divide first, then round it.

ROUND(number_x/1000)