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 is a state like
It is like a box that contains nothing.
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.
select (CASE WHEN ""=NULL THEN 1 ELSE 0 END) as null_is_blank from dual;
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.
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
So it takes 2 steps.
- Replace blank with NULL by
- Then replace NULL with specific value by
ifnull(nullif(target column,''),'specific value')
- 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.