CODEWITHSUNDEEP
stringfloating-pointgoogle-bigqueryprecision
Pratik Patil
Pratik Patil

Reputation: 832

Google Bigquery - Representing float as string without loosing the precision

In Google bigquery, just curious: is there a simple way to present float value as string without loosing the precision of it.

Currently: 

select 
  string(float_value) as str_float_value,
  cast(float_value as string) as cast_float_value_as_string, 
  float_value as original_float_value
from  (select 33.7474782 as float_value);

this will give you back: 

Row | str_float_value | cast_float_value_as_string | original_float_value
----+-----------------+----------------------------+---------------------
1   | 33.7475         | 33.7475                    | 33.7474782

Is there a better way to get float as string without loosing it's precision ?

[Context: I have some use case where there are 2 different tables one with string column containing fractional values and other table containing regular float column, I can always cast string to float and then join will work. But just curious if there was any way we could get float as string without loosing the precision]

Upvotes: 2

Views: 3021

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

It looks like legacy SQL preserves less precision when casting floating point values to strings. Can you use standard SQL instead? Here is what I see:

$ bq query "SELECT CAST(float_value AS STRING) AS float_string, float_value FROM (SELECT 33.7474782 AS float_value);"
+--------------+-------------+
| float_string | float_value |
+--------------+-------------+
| 33.7475      |  33.7474782 |
+--------------+-------------+

$ bq query --use_legacy_sql=false "SELECT CAST(float_value AS STRING) AS float_string, float_value FROM (SELECT 33.7474782 AS float_value);" 
+--------------+-------------+
| float_string | float_value |
+--------------+-------------+
| 33.7474782   |  33.7474782 |
+--------------+-------------+

Upvotes: 3

Related Questions