Format a number to have commas (1000000 -> 1,000,000)

Question

In Bigquery: How do we format a number that will be part of the resultset to have it formatted with commas: like 1000000 to 1,000,000 ?

Answer 1

below is 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 will need floats too, 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
)

added for Legacy SQL

Btw, if for whatever reason you are bound to Legacy SQL - below is 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,') 
    });
  }"
)

Above example uses in-line versin of Legacy SQL User-Defined Functions which is usually used for quick demo/example - but not recommended in production - if you will find it useful for you - you will need to "very slightly" transform it - see https://cloud.google.com/bigquery/user-defined-functions#webui for example