MySQLでファイルパスを格納したテーブルから部分文字列（サブストリング）を取得する方法

MySQLのsubstr()関数で部分文字列を取得する基本構文

MySQLで文字列の一部（部分文字列）を取得するには、substr()関数を使用します。基本的な構文は以下のとおりです。

select substr(カラム名, 開始位置, 終了位置) from テーブル名 limit 値;

select substr(カラム名, 開始位置 + 終了位置) from テーブル名 limit 値;

第1引数に対象のカラム、第2引数に抽出を開始する位置、第3引数に終了位置を指定します。終了位置を省略した場合は、開始位置から文字列の末尾までがすべて抽出されます。

サンプルテーブルの作成

まず、ファイルパスを格納するテーブルを作成しましょう。

mysql> create table demo11
-> (
-> id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> fileLocation text
-> );
Query OK, 0 rows affected (2.60 sec)

fileLocationカラムにはファイルの保存場所となるパス文字列が格納されます。

レコードの挿入

insertコマンドを使用して、サンプルデータとしてファイルパスをいくつか登録します。

mysql> insert into demo11(fileLocation) values('E:/users/program/sample.sql');
Query OK, 1 row affected (0.15 sec)

mysql> insert into demo11(fileLocation) values('E:/users/data/db.sql');
Query OK, 1 row affected (0.32 sec)

mysql> insert into demo11(fileLocation) values('C:/users/data/sample2.sql');
Query OK, 1 row affected (0.13 sec)

登録したレコードの確認

select文でテーブル内のデータを表示して確認してみましょう。

mysql> select *from demo11;

実行すると、次のような出力が得られます。

+----+-----------------------------+
| id | fileLocation                |
+----+-----------------------------+
|  1 | E:/users/program/sample.sql |
|  2 | E:/users/data/db.sql        |
|  3 | C:/users/data/sample2.sql   |
+----+-----------------------------+
3 rows in set (0.00 sec)

部分文字列を分割して複数行で取得する

ここからは、文字列を前半と後半に分けて、それぞれ別のクエリで取得する方法を見ていきましょう。

【前半部分】1文字目から15文字目までを抽出するクエリ：

mysql> select substr(fileLocation,1,15) from demo11 limit 1,2;

開始位置「1」、終了位置「15」を指定しているため、先頭から15文字分が抽出されます。limit 1,2により、2行目から2件分のレコードが対象となります。実行結果は以下のとおりです。

+---------------------------+
| substr(fileLocation,1,15) |
+---------------------------+
| E:/users/data/d           |
| C:/users/data/s           |
+---------------------------+
2 rows in set (0.00 sec)

【後半部分】16文字目以降を抽出するクエリ：

mysql> select substr(fileLocation,16) from demo11 limit 1,2;

開始位置「16」のみを指定しているため、16文字目から文字列の末尾までが抽出されます。実行結果は以下のとおりです。

+-------------------------+
| substr(fileLocation,16) |
+-------------------------+
| b.sql                   |
| ample2.sql              |
+-------------------------+
2 rows in set (0.00 sec)

まとめ

substr()関数を使えば、開始位置と終了位置を指定するだけで、ファイルパスなどの長い文字列から任意の範囲を簡単に取り出せます。終了位置を省略すれば末尾まで一括取得でき、limit句と組み合わせれば対象行を絞り込むことも可能です。ログ解析やパス文字列の加工など、さまざまな場面で活用できる便利な関数なので、ぜひ使いこなしてみてください。