How to truncate after 4 digits / decimal places in Bigquery Numeric columns ?

There is no Decimal datatype in BigQuery. You will have to use NUMERIC. And there is no way to specify the precision of decimals in a NUMERIC Datatype. Often we end up with values like this :

0.029*50/100=0.014500000000000002

There is a way to TRUNC the decimal places in Bigquery using the TRUNC() function. It removes all digits to the right of the decimal point for any value. Optionally, you can specify the number of digits to which to round. Input can be an Integer, a Decimal, a column reference, or an expression

Select TRUNC(COLUMN_NAME,4) from Mydataset.MyTable;

This will TRUNC the results to 4 decimal places

To Truncate all the decimals from a Numeric column:

Just use the TRUNC Function without any second parameter:

Select TRUNC(COLUMN_NAME) from Mydataset.MyTable;

To Truncate to 2 decimals in a Numeric column:

Select TRUNC(COLUMN_NAME,2) from Mydataset.MyTable;

Results:

nVector

posted on 14 May 20