Output numbers with comma separation in snowflake result set

user_13641962 0
Viewed 8464

I'm working with SQL queries in Snowflake. I'm able to work in the browser (chrome), or on SQLWorkbenchJ. I'm comfortable with both, but prefer the browser.

I'm working with moderately large numbers, up into the hundreds of billions (10^8), so it would be really helpful if I could make the program print my numbers with comma separation for the thousands (e.g. 12,345,678,901.00 vs. 12345678901.00).

I've looked into the documentation on format models here https://docs.snowflake.com/en/sql-reference/sql-format-models.html but I can't see an option to output in the style I'm looking for.

Even besides that, I would really prefer to implement this as a configuration to the interface itself rather than code I apply to my queries.

snowflake-cloud-data-platform resultset format configuration sql
2 Answers

The numeric format models appears to cover your need via the 9 (nine), , (comma), and . (period) symbols. Quoting some relevant portions from the documentation:

9: Position for a digit; leading/trailing zeros are replaced with blank spaces.

0: Position for a digit; leading/trailing zeros are explicitly printed.

. (period): Decimal fraction separator; always printed as a period.

, (comma): Digit group separator; printed as a comma or blank space.

[...]

The digit group separator , (comma) or G results in the corresponding group separator character being printed if the number is big enough so the digits are on the both sides of group separator. An example of a format model useful for printing currency sums would be 999,999.00.

Here are some adapted examples demonstrating the required format:

SELECT to_varchar(123.21, '999,999,999,999.00');
SELECT to_varchar(12345.00, '999,999,999,999.00');
SELECT to_varchar(12345678, '999,999,999,999.00');
SELECT to_varchar(12345678901, '999,999,999,999.00');
SELECT to_varchar(12345678901.59, '999,999,999,999.00');
Admin1

Looks like your wish has been partially granted!

In Snowflake's new web interface (they call it Snowsight), you can choose to display numbers using commas as the separator for thousands.

It only affects the display in the UI though - it doesn't work for exporting data (which shouldn't be a problem, because exported data shouldn't be formatted - that's something excel or other can take care of).

enter image description here

user_44067930
Related