CODEWITHSUNDEEP
sqlgoogle-bigquery
florian
florian

Reputation: 501

BigQuery SQL: percent with 2 decimal places

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!

Upvotes: 5

Views: 25119

Answers (4)

owlstar-lord
owlstar-lord

Reputation: 11

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

Upvotes: 1

nick windridge
nick windridge

Reputation: 1

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

Upvotes: 0

Alexandre Moraes
Alexandre Moraes

Reputation: 4042

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

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Doesn't round() do what you want?

SELECT ROUND((val_1 / val_2), 2)

Upvotes: 5

Related Questions