DB 開発

Replace blank column in MySQL

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.

コチラもオススメ

KRSW

駆け出し機械学習エンジニア。機械学習、DB、WEBと浅く広い感じ。 Junior machine learning engineer. Not a specialist but a generalist who knows DB, WEB too.

If you felt this article is useful, please share.

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

-DB, 開発
-

Translate »

Copyright© ITips , 2020 All Rights Reserved.