Updated:
テクニカルブログ

SQLで文字列を分割する方法（MySQL）

背景

あるサービスでユーザーはメールアドレスを登録するのですが、そのメールアドレスはどういったドメインが多いのか調べようと思いました。

最初に思いついた方法は、メールアドレスを@で分割して、分割した2つの文字列の2番目がドメインになっていると思うので、それでgroup byするというものでした。

ですが、SQLで文字列を分割する方法を知らなかったので、メモしておきます。

文字列の分割

MySQLにはsubstring_indexという関数があります。
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_substring-index

substring_indexの引数

substring_indexは

1
substring_index(分割対象の文字列, 区切り文字, 返す文字列の数)

となっています。

分割対象の文字列と区切り文字については問題ないと思いますが、返す文字列の数はわかりづらいですね。

公式に書いてある例を手元で実行してみます。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+------------------------------------------+
| www.mysql                                |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
+-------------------------------------------+
| mysql.com                                 |
+-------------------------------------------+
1 row in set (0.00 sec)

最初の例はドットで分割して、前から2つなので、www.mysqlが、後ろの例はドットで分割して、-2なので後ろから2つなので、mysql.comが返ってきます。

メールアドレスのドメイン取得

ではメールアドレスのドメインを取得してみようと思います。

1
2
3
4
5
6
7
mysql> SELECT SUBSTRING_INDEX('user@mysql.com', '@', -1);
+--------------------------------------------+
| SUBSTRING_INDEX('user@mysql.com', '@', -1) |
+--------------------------------------------+
| mysql.com                                  |
+--------------------------------------------+
1 row in set (0.00 sec)

できました！

分割文字列の真ん中を取得したい場合

ふと思ったんですが、www.mysql.comのmysqlのみを取得したい場合はどうするのでしょうか。

1
2
3
4
5
6
7
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
+--------------------------------------------------------------------+
| mysql                                                              |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

こうかな？と思ったんですが、ちょっと煩雑ですね…

メールアドレスのドメインで集計

以上のことがわかったので、集計するクエリを書いてみます

1
2
3
4
5
6
7
8
SELECT
  SUBSTRING_INDEX(email, '@', -1) as domain,
  COUNT(*)
FROM
  users
GROUP BY
  domain
;

無事できました。

まとめ

MySQLで文字列を分割する方法を紹介しました。今回の関数はMySQL独自のものですが、その他のプラットフォームでも同じような関数はあると思います。

文字列を操作して、クエリを実行できると集計できる幅も広がりそうですね。久々SQLを書いたのですが、分析は楽しいですね！