Format the number with commas (1,000,000 & # 8594; 1,000,000)

Question

In Bigquery: how do we format a number that will be part of a result set so that it is formatted with commas: like 1000000-1000000?

Answer 1

below for standard SQL

SELECT 
  input,
  FORMAT("%'d", input) as formatted
FROM (
  SELECT 123 AS input UNION ALL 
  SELECT 1234 AS input UNION ALL
  SELECT 12345 AS input UNION ALL
  SELECT 123456 AS input UNION ALL
  SELECT 1234567 AS input UNION ALL
  SELECT 12345678 AS input UNION ALL
  SELECT 123456789 AS input
)

Works great for integers, but if you need floats, you can use:

SELECT 
  input,
  CONCAT(FORMAT("%'d", CAST(input AS int64)), 
         SUBSTR(FORMAT("%.2f", CAST(input AS float64)), -3)) as formatted
FROM (
  SELECT 123 AS input UNION ALL 
  SELECT 1234 AS input UNION ALL
  SELECT 12345 AS input UNION ALL
  SELECT 123456.1 AS input UNION ALL
  SELECT 1234567.12 AS input UNION ALL
  SELECT 12345678.123 AS input UNION ALL
  SELECT 123456789.1234 AS input
)

for legacy sql

Btw, if for some reason you are attached to Legacy SQL - below is a quick example for it

SELECT input, formatted
FROM JS((
  SELECT input
  FROM 
    (SELECT 123 AS input ),
    (SELECT 1234 AS input ),
    (SELECT 12345 AS input ),
    (SELECT 123456 AS input ),
    (SELECT 1234567 AS input ),
    (SELECT 12345678 AS input ),
    (SELECT 123456789 AS input)
  ),
  // input
  input,
  // output
  "[
  {name: 'input', type:'integer'},
  {name: 'formatted', type:'string'}
  ]",
  // function
  "function (r, emit) {
    emit({
      input: r.input,
      formatted: r.input.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') 
    });
  }"
)

, SQL, /, - - " " - . https://cloud.google.com/bigquery/user-defined-functions#webui

Answer 2

SQL:

SELECT FORMAT("%'d", 1000123)

1,000,123   

SQL: https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql