Search code examples
mysqlstringnulldata-cleaningdata-processing

MySQL: Replacing 'NULL' as string with actual NULL values in whole table with single query

When I try to query:

SELECT * 
FROM my_table
where my_column is Null

it returns 0 results. The column is integer.

But when I do:

SELECT * 
FROM my_table
where my_column = 'Null'

it returns expected results. And interestingly returned rows has 0 value.

And before I did 

update my_table set my_column = Null where my_column = '0';

it used to return '0'.

What could be the reason of this, and what would be the possible solution to have them NULL instead of 'NULL' or '0' or 0.

This is driving me crazy and I spend more then 4 hours trying to fix this. My table has these non-sense values all around. So if any method to fix this for my table instead of single column that would be better.

here is a picture with is NULL here is a picture with is NULL

here is a picture with 'NULL' here is a picture with = 'NULL'

and here is a picture with is NULL working as expected in different column.

enter image description here

Solution

  • You say that the column my_column's data type is INTEGER, so it is impossible that the column contains the value 'NULL'.
    When you apply this condition:

    where my_column = 'Null'

    the string literal 'Null' is implicitly converted to an integer and since this conversion can not succeed the result is 0 and your condition is equivalent to:

    where my_column = 0

    You can update your table like this:

    update tablename
set my_column = null
where my_column = 0