BigQuery SQL: percent with 2 decimal places

Question

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!

Answer 1

Doesn't round() do what you want?

SELECT ROUND((val_1 / val_2), 2)

Answer 2

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.

Answer 3

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}'), ' %')
);