DB Dev

Replace blank column in MySQL

Share this for your friends.

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 is a state like "".
It is like a box that contains nothing.


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.


   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.

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')


  • 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.

Share this for your friends.

If you felt this article is useful, please share.

にほんブログ村 IT技術ブログへ

-DB, Dev

© 2024 ITips