sqlconfigurationformatresultsetsnowflake-cloud-data-platform

Output numbers with comma separation in snowflake result set

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.

Solution

  • 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');