Can we cast the type in BigQuery?

Question

Following my query :

SELECT SQRT((D_o_latitude - T_s_lat)^2+(D_o_longitude - T_s_long)^2)/0.00001 FROM [datasetName.tableName]

I am getting the error as Error: Argument type mismatch in function SUBTRACT:'D_o_latitude' is type string, 'T_s_lat' is type string

So Let me know can we convert the string type to float in the query some thing like casting the data type, I can not change the datatype

Answer 1

With standard SQL you can use CAST function, eg. cast(numStringColumn as int64). Look out for standard SQL type names, as they aren't exactly same as legacy SQL.

Answer 2

Under legacy SQL, you can cast types in BigQuery using the following functions: INTEGER(), STRING(), BOOLEAN() TIMESTAMP(), and FLOAT().

Use cast() for standard SQL (see opensourcegeek's answer).

In your case, you could write:

SELECT SQRT((INTEGER(D_o_latitude) - T_s_lat)^2+(INTEGER(D_o_longitude) - T_s_long)^2)/0.00001 
FROM [datasetName.tableName]