SQLの空白判定のサンプルコード 連続した空白を判定する方法

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関数を使うとレスポンスが悪くなるので、本来はアプリでデータチェックがベスト