How to turn numeric value into currency in BigQuery?

Question

I am new to BigQuery and am trying to convert numeric values (from Salesforce) to currency (preferably dollar value).

Very basically, what I have currently is:

SELECT salesforce.Name,
       ROUND(salesforce.Amount,2) as Amount 

FROM table.salesforce

Which obviously only rounds the value to two decimal places.

Answer 1

Regarding your question about how to convert a numeric value to currency value in BigQuery, I would advise you to use the FORMAT() and CONCAT() built-in functions.

I see that in your question you mentioned you want to round the numeric values to the second decimal place, you can do that using FORMAT(), you can read more about it here. In addition, to use the "$" sign, you can use CONCAT(). Below is an example where I used some dummy data to exemplify what I explained:

  WITH
  data AS (
  SELECT
    20.21 AS num
  UNION ALL
  SELECT
    99999999.12 AS num
  UNION ALL
  SELECT
    12345 AS num )
  SELECT
  CONCAT('$ ',FORMAT("%'.2f", num)) AS new_num
  FROM
  data

And the output:

Notice that in the FORMAT() function I used "%'.2f", which rounds the number to the second decimal place. You can find more information about the meaning of each letter/number in the expression using the following guide.

As a bonus information, the currency values are formatted in a way that the dot "." is a decimal separator and the comma "," is a grouping separator. You can switch that using regex expressions with REGEX_REPLACE() and REPLACE() functions. If that is the case, just let me know so I can help.