CODEWITHSUNDEEP
google-bigquery
Evans Gunawan
Evans Gunawan

Reputation: 113

Data cleaning in bigquery sql changing from float to integer

Hi I have a table where I want to alter the values in the columns from float to integer and replace the values and keep it as string. How would I best do this?

The table is as follows:

id discount product
1 10 dettol soap
2 10.0 dettol soap green
3 0 dettol soap red
4 x dettol soap blue
5 dettol soap yellow
6 5 dettol soap beige
9 13.3210282172312131 dettol soap white

I want to clean the discount table into the same table

id discount product
1 10 dettol soap
2 10 dettol soap green
3 0 dettol soap red
4 0 dettol soap blue
5 0 dettol soap yellow
6 5 dettol soap beige
9 13 dettol soap white

What I have is select id , cast(cast(discount as integer) as string) , product from table

How do you do a replace non numbers and blanks into 0 and decimals into integer and cast as string and replace the values into the current table?

Upvotes: 0

Views: 3672

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

Consider below

select * 
  replace(
    format('%.0f', ifnull(safe_cast(discount AS float64), 0))
  as discount)
from your_table

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Jaytiger
Jaytiger

Reputation: 12234

Consider this:

SELECT CAST(IFNULL(CAST(SAFE_CAST(x AS FLOAT64) AS INT64), 0) AS STRING) AS discount 
  FROM UNNEST(['10', '10.0', '0', 'x', '', '5', '13.3210282172312131']) x;

Or, you can try it with a regular expression.

SELECT IFNULL(REGEXP_EXTRACT(x, r'^[0-9]+'), '0') discount
  FROM UNNEST(['10', '10.0', '0', 'x', '', '5', '13.3210282172312131']) x;

output:

enter image description here

Upvotes: 3

Related Questions