CODEWITHSUNDEEP
google-bigquery
Steve Scherer
Steve Scherer

Reputation: 345

Is there a working FORMAT_STRING or TEXT function?

I've seen another post about two years ago about a possible FORMAT_STRING. I'm looking for something like this so if I have a string such as 123456789, I would be able to format it to include appropriate commas so it looks like, 123,456,789.

In Google Sheets there is a TEXT function that enables a variety of formatting. If I were to do this in Google Sheets I could do, =TEXT("123456789", "#,##0"). Is there a function or plan to have a function that would enable text formatting such as what exists in the Google Sheets TEXT function? There are many cases where this would come in handy when I am creating reports and need to format FLOAT or INTEGER data as a string in order to concatenate with other fields, yet still preserve commas or percentages where appropriate.

Upvotes: 1

Views: 2776

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

when I do really need to do this (creating reports) whithin the BigQuery, I am using below approach (simplified example) 

SELECT input, output FROM JS(
// input table 
(
  SELECT input FROM 
    (SELECT 123 AS input), 
    (SELECT 1234 AS input), 
    (SELECT 12345 AS input), 
    (SELECT 123456.1 AS input), 
    (SELECT 1234567.12 AS input), 
    (SELECT 12345678.123 AS input),
    (SELECT 123456789.123 AS input)
), 
// input columns
  input,
// output schema
"[
  {name: 'input', type: 'float'},
  {name: 'output', type: 'string'}
]",
// function
"function(r, emit){
  emit({
    input: r.input,
    output: r.input.toFixed(2).toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,')
  });
}"
)

Btw, in recently introduced standard SQL dialect - you can use FORMAT() function.
Like in below example.
Don't forget to uncheck Use Legacy SQL checkbox under Show Options

SELECT 
  input,
  FORMAT("%'d", input) AS output
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
)

or for floats 

SELECT 
  input,
  CONCAT(FORMAT("%'d", CAST(input AS int64)), 
         SUBSTR(FORMAT("%.2f", CAST(input AS float64)), -3)) 
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
)

Upvotes: 5

Related Questions