CODEWITHSUNDEEP
google-bigquery
Sunil
Sunil

Reputation: 111

Standard SQL throwing BAD INT errror

How to avoid errors like "Bad int64 value: 72\017" in standard SQL in BQ? Please let me know the methods to convert \017 to \17

Upvotes: 0

Views: 1475

Answers (1)

Adrian White
Adrian White

Reputation: 1804

You may need to escape the backslash if that's in your data ... then perhaps have a peak at REGEXP_REPLACE and SAFE_CAST ... this might be a good approach if your data is somewhat dirty.

#standard sql :

with cte as ( select 72 a , '\\01d7f' b union all select 82 , '\\018'  )

select a , REGEXP_REPLACE(b,'[^0-9 ]','') , a / safe_cast(REGEXP_REPLACE(b,'[^0-9 ]','') as numeric) from cte

a   f0_ f1_
72  017 4.235294118
82  018 4.555555556

Upvotes: 1

Related Questions