BigQuery SQL: percent with 2 decimal places

user_5981601 0
Viewed 10648

In BigQuery standard SQL, I need to present a result of division as percentage with 2 decimal places, e.g. 21.23%. The only way I've been able to achieve this is by the doing the following:

  1. ROUND the result to 2 decimal places
  2. CAST it as a string
  3. FORMAT to add the percent character

FORMAT('%s%%', CAST(ROUND((val_1 / val_2) * 100, 2) AS STRING))

It feels like a workaround...

Is there a proper way of doing it? Primarily, I'd like to keep the output as a float, not a string.

Thanks!

google-bigquery sql
4 Answers

Your approach will work. However, I would advise you to create a TEMP FUNCTION. In case you are going to use it a couple of times, it will make your code look better and easier to read.

The syntax would be as follows:

#standardSQL
CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  CONCAT(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}'), ' %')
);
SELECT 
  NUMFORMAT(888888.999), 
  NUMFORMAT(123456.99),
  NUMFORMAT(74185.009),
  NUMFORMAT(-10.789)

Notice that I am assuming the data you want to format as percentiles is a FLOAT64. Although, if it is not a FLOAT64 you can change that in the function. And the output is:

enter image description here

If your percentiles are too high, you may also want to format the integer part of the number. The syntax would be as follows: 

#standardSQL
CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  #CONCAT(REPLACE(REPLACE(REGEXP_REPLACE(FORMAT("%'.2f", number), r'([\d,]+).(\d+)', r'\1=\2'), ',', '.'), '=', ','),'%')
  #CONCAT(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}'), ' %')
  CONCAT(format("%'.2f", cast(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}') as float64)), '%')
  );
SELECT 
  NUMFORMAT(888888.999), 
  NUMFORMAT(123456.99),
  NUMFORMAT(74185.009),
  NUMFORMAT(-10.789)

The output would be:

enter image description here

user_125713870

Doesn't round() do what you want?

SELECT ROUND((val_1 / val_2), 2)
user_11440350

as @nick winridge pointed out, there's an issue with less than 2 non-zero decimal places. This is a little hack that I added to fix that..

CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  CONCAT(REGEXP_EXTRACT(cast(((number + 0.000001) as string), r'\d*\.\d{2}'), ' %')
);
user_181659460

I realise this is an old post but alexandre moraes's answer won't work if there aren't non-zeros beyond the 2nd decimal place in the argument passed to the function.

CREATE TEMP FUNCTION NUMFORMAT(number FLOAT64) AS (
  CONCAT(REGEXP_EXTRACT(cast(number as string), r'\d*\.\d{2}'), ' %')
);
SELECT 
  NUMFORMAT(33.33212121), 
  NUMFORMAT(100.00000),
  NUMFORMAT(100.00),
  NUMFORMAT(100),
  NUMFORMAT(100.00111)

gives result: null values if less than 2 non-zero decimal places. image

user_27758930
Related