MySQLでハイフン区切りの文字列値を1つの列から複数の列に分離して選択する方法

ハイフンで区切られた文字列値を1つの列から複数の列に分離して取得したい場合、MySQLの SUBSTRING_INDEX() 関数を使用すると簡単に実現できます。ここでは、サンプルテーブルを使って具体的な手順を解説します。

サンプルテーブルの作成

まず、従業員情報を格納するテーブルを作成します。

mysql> create table DemoTable1962
    (
    EmployeeInformation text
    );
Query OK, 0 rows affected (0.00 sec)

データの挿入

次に、INSERT文を使って「ID-名前-年齢」の形式でハイフン区切りのデータを挿入します。

mysql> insert into DemoTable1962 values('101-John-29');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1962 values('102-David-35');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1962 values('103-Chris-28');
Query OK, 1 row affected (0.00 sec)

登録データの確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> select * from DemoTable1962;

実行結果は以下のとおりです。

+---------------------+
| EmployeeInformation |
+---------------------+
| 101-John-29         |
| 102-David-35        |
| 103-Chris-28        |
+---------------------+
3 rows in set (0.00 sec)

文字列を複数の列に分離するクエリ

以下のクエリを実行すると、1つの列に格納された値を「従業員ID」「氏名」「年齢」の3つの列に分離して取得できます。

mysql> select substring_index(EmployeeInformation, '-', 1) as EmployeeId,
    substring_index(substring_index(EmployeeInformation,'-',2),'-',-1) AS EmployeeName,
    substring_index(substring_index(EmployeeInformation,'-',-2),'-',-1) AS EmployeeAge
    from DemoTable1962;

SUBSTRING_INDEX() の仕組み

SUBSTRING_INDEX(文字列, 区切り文字, 出現回数) は、指定した区切り文字がN回現れるまでの部分文字列を返します。正の数を指定すると左から、負の数を指定すると右から数えます。中間の要素（氏名など）を取り出す場合は、SUBSTRING_INDEX を入れ子にすることで、目的の位置の値だけを抽出できます。

上記クエリの実行結果は以下のとおりです。

+------------+--------------+-------------+
| EmployeeId | EmployeeName | EmployeeAge |
+------------+--------------+-------------+
| 101        | John         |          29 |
| 102        | David        |          35 |
| 103        | Chris        |          28 |
+------------+--------------+-------------+
3 rows in set (0.00 sec)

このように SUBSTRING_INDEX() を活用すれば、区切り文字で連結された文字列データでも、SQLだけで柔軟に分割・抽出することが可能です。