SQLの空白判定についてまとめています。
SQLの空白判定には、TRIMを条件式に組み込む
SQLの空白判定を実際のデータを使って試してみましょう。
以下、MySQLで実行しています。サンプルデータベースとして、employeesを使用しています。
【関連記事】
▶MySQLの入門には、GUIツールで慣れ、サンプルDBを使った学習が効果的
まずは、テーブルemployeesのコピー、employees_copyを作成します。
mysql> create table employees_copy select * from employees; Query OK, 300025 rows affected (3.72 sec) Records: 300025 Duplicates: 0 Warnings: 0 mysql> select * from employees_copy limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec)
データのemp_no 10001～10005をupdateで更新し、first_nameカラムに空白、複数の空白、文字列の前後に空白、NULLの入ったデータを作成します。
mysql> update employees_copy set first_name=" " where emp_no=10001; Query OK, 1 row affected (0.24 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update employees_copy set first_name=" " where emp_no=10002; Query OK, 1 row affected (0.22 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update employees_copy set first_name=" " where emp_no=10003; Query OK, 1 row affected (0.27 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update employees_copy set first_name=" Chirstian " where emp_no=10004; Query OK, 1 row affected (0.22 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update employees_copy set first_name=NULL where emp_no=10005; Query OK, 1 row affected (0.22 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employees_copy limit 10; +--------+------------+--------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+--------------+-----------+--------+------------+ | 10001 | 1953-09-02 | | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | NULL | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+--------------+-----------+--------+------------+ 10 rows in set (0.00 sec)
わかりやすいよう、first_nameカラムの前後にシングルクォーテーションを連結して表示。
mysql> select emp_no, concat("'",first_name,"'") as first_name from employees_copy limit 10;
+--------+----------------+
| emp_no | first_name |
+--------+----------------+
| 10001 | ' ' |
| 10002 | ' ' |
| 10003 | ' ' |
| 10004 | ' Chirstian ' |
| 10005 | NULL |
| 10006 | 'Anneke' |
| 10007 | 'Tzvetan' |
| 10008 | 'Saniya' |
| 10009 | 'Sumant' |
| 10010 | 'Duangkaew' |
+--------+----------------+
10 rows in set (0.00 sec)
単一の空白、複数の空白にマッチさせる条件として、 trim(first_name) = ” を指定しています。trimは文字列の前後から空白を取り除く関数で、空白のみのカラムは「空文字」になります。空文字とNULLは扱いが異なるため、NULLにもマッチさせるために条件にIS NULLをorで接続しました。
mysql> select * from employees_copy where trim(first_name) = '' or trim(first_name) IS NULL; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | | Bamford | M | 1986-08-28 | | 10005 | 1955-01-21 | NULL | Maliniak | M | 1989-09-12 | +--------+------------+------------+-----------+--------+------------+ 4 rows in set (0.19 sec)
目的の動作が達成できましたが、where句でtrimを使用する場合、件数によってはパフォーマンスが悪くなります。本来なら、複数の空白文字が挿入されないよう、アプリケーション等でチェックするのがベストと言えるでしょう。
まとめ
ポテパンダの一言メモ
- 連続した空白文字は、whereでtrim後の文字列と空文字を比較でマッチ可能
- 空文字とNULLは別扱いなので、IS NULLを組み合わせる必要がある
- where句でtrim関数を使うとレスポンスが悪くなるので、本来はアプリでデータチェックがベスト
