Missing values were incorrectly entered as zeroes

Question

How do I change 0 values to null?

Missing values were incorrectly entered as zeroes, and you need to change them to null values before you look for trends. The code you’re using to do this is returning an error message.

Answer 1

You need to provide more context. Is this on BigQuery ? What program is returning the error message?

If your data is a BigQuery table, try this to replace the zeros by nulls:

SELECT IF(your_column = 0, null, your_column)

Answer 2

There are 2 possible solutions to your question, based on the exact need:

If your wish is to query the existing values and treat the 0 values as if they were nulls, the simple solution is to use NULLIF(column_name, 0) or NULLIF(column_name, '0') instead of column_name - depending if the values are integers or strings.

Or - if you mean to update the table and get rid of the 0 values and nullify them, you should use an update query like so:

UPDATE my_dataset.my_table
SET column_name = NULLIF(column_name, 0) //(or '0')
WHERE TRUE