Replace blank column in MySQL

Replace blank column in MySQL.

Sometimes you want to run some special process when a specific column has no value in database.
But in case of MySQL, it is not easy to find no value.

There are 2 types of "No value"

You may think that "No value" is "No value".
But there are 2 types of "No value" in database.

• blank
• NULL

blank

Blank is a state like "".
It is like a box that contains nothing.

NULL

NULL is NULL in database.
It is like not prepared state.

blank is not same as NULL

According to the SQL below, blank is not same as NULL.

SQL

select 
  (CASE WHEN ""=NULL THEN 1
   ELSE  0 END) as null_is_blank
from dual;

Result

null_is_blank
0

But in case of !='', blank seems same as NULL.

SELECT * FROM tbl_name WHERE str != '';
→ blank and NULL are excluded.
NULL and blank in MySQL

So in order to deal with "No value", we should replase NULL or blank.

Replace blank

Then we try to replace blank.
But still we have a problem.
In Oracle, we can use DECODE to replace some specific value with new one.
In MySQL, we don't have DECODE.
So it takes 2 steps.

• Replace blank with NULL by nullif
• Then replace NULL with specific value by ifnull

ifnull(nullif(target column,''),'specific value')

Finally

• There are different types of "No value". Blank and NULL.
• MySQL doesn't have DECODE function.
• In order to replace blank, you should replace it with NULL and replace NULL with specific value.