0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

MySQLでSELECTにて抽出するときに出る空文字をNULLに変換する。

Posted at

MySQLで空白の文字列をNULLに変換する方法を記載します。

Code Name Continent IndepYear test
ABW Aruba North America NULL
AFG Afghanistan Asia 1919
AGO Angola Africa 1975
AIA Anguilla North America NULL

というテーブルがあったとします。
このtestカラムの中の空文字をSELECTで抽出するときは全てNULLに変えたい場合です。

SELECT
Code,
Name,
Continent,
IndepYear,
nullif(test,'')
FROM
country
;

とSELECT文を書き実行すると、

Code Name Continent IndepYear nullif(test,'')
ABW Aruba North America NULL NULL
AFG Afghanistan Asia 1919 NULL
AGO Angola Africa 1975 NULL
AIA Anguilla North America NULL NULL

このように表示されます。

nullif(カラム名,'NULLに変えたい文字列')
ということになります。

カラム名を整えたいなら、

SELECT
Code,
Name,
Continent,
IndepYear,
nullif(test,'') as test
FROM
country
;

としてあげれば

Code Name Continent IndepYear test
ABW Aruba North America NULL NULL
AFG Afghanistan Asia 1919 NULL
AGO Angola Africa 1975 NULL
AIA Anguilla North America NULL NULL

になります。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?