MySQLで列（カラム）を分割する方法！SUBSTRING_INDEX関数の使い方を徹底解説

MySQLで列を分割するには？SUBSTRING_INDEX関数の基本

MySQLで1つの列に格納された文字列を区切り文字で分割したい場合、SUBSTRING_INDEX()関数を使用します。この記事では、アンダースコア（_）で区切られた氏名データを分割する具体例を通じて、その使い方をわかりやすく解説します。

1. サンプルテーブルの作成

まず、以下のコマンドでテーブルを作成します。

mysql> create table DemoTable
    -> (
    -> Name varchar(40)
    -> );
Query OK, 0 rows affected (1.80 sec)

2. レコードの挿入

INSERT文を使って、アンダースコアで区切られた氏名データを登録します。

mysql> insert into DemoTable values('John_Smith');
Query OK, 1 row affected (0.36 sec)
mysql> insert into DemoTable values('Carol_Taylor');
Query OK, 1 row affected (0.28 sec)
mysql> insert into DemoTable values('David_Miller');
Query OK, 1 row affected (0.54 sec)

3. 登録データの確認

SELECT文でテーブル内の全レコードを表示します。

mysql> select *from DemoTable;

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

+--------------+
| Name         |
+--------------+
| John_Smith   |
| Carol_Taylor |
| David_Miller |
+--------------+
3 rows in set (0.00 sec)

4. 列を分割するクエリの実行

以下のクエリで、アンダースコアより前の部分（名）だけを取り出すことができます。

mysql> select if(locate('_',Name)=0,'',substring_index(Name, '_', 1)) from DemoTable;

実行結果：

+---------------------------------------------------------+
| if(locate('_',Name)=0,'',substring_index(Name, '_', 1)) |
+---------------------------------------------------------+
| John                                                    |
| Carol                                                   |
| David                                                   |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

クエリの仕組みを解説

このクエリは、3つの関数を組み合わせて構成されています。

• locate('_', Name)：列「Name」の中にアンダースコア（_）が存在する位置を返します。見つからない場合は0を返します。
• if(locate('_',Name)=0,'', ...)：アンダースコアが含まれていない場合は空文字列を返し、含まれている場合は分割処理を実行します。これにより、区切り文字がないデータでもエラーなく処理できます。
• substring_index(Name, '_', 1)：区切り文字「_」より前の部分（1番目の要素）を取り出します。例えば「John_Smith」であれば「John」が返されます。

応用：区切り文字より後ろの部分を取得する方法

逆に、区切り文字より後ろの部分（姓）を取得したい場合は、SUBSTRING_INDEXの第3引数に「-1」を指定します。

mysql> select substring_index(Name, '_', -1) from DemoTable;

このクエリを実行すると、「Smith」「Taylor」「Miller」が取得できます。正の値を指定すると左から、負の値を指定すると右から数えた位置の文字列が抽出される点を覚えておくと便利です。

まとめ

MySQLで列内の文字列を分割するには、SUBSTRING_INDEX()関数が非常に有効です。locate()関数やif()関数と組み合わせることで、区切り文字が存在しないデータへの対応も可能になります。氏名や住所、タグなど、区切り文字で結合されたデータを扱う際にぜひ活用してください。