In Snowflake, how can i format the numbers with "M" and "B" suffix for Millions and Billions

Question

In Snowflake, how can i format the numbers with "M" and "B" suffix for Millions and Billions like display the number as 10M instead of 10,000,000

and 10B instead of 10,000,000,000

i tried formatting the numbers, but couldn't not achieve as desired.

Answer 1

These examples should show you how to achieve what you want:

select to_char(round(10000000/1000000,0))||'M'; -- 10M

select to_char(round(12314578/1000000, 2))||'M'; -- 12.31M