MySQLのクエリ結果としてファイルの拡張子を取得するにはどうすればよいですか？

MySQLでファイル拡張子を取得する方法：SUBSTRING_INDEX()関数の使い方

SQLクエリの結果としてファイルの拡張子を取得したい場合は、MySQLのSUBSTRING_INDEX()関数を使用すると簡単に実現できます。この関数は、指定した区切り文字を基準に文字列を分割し、必要な部分だけを取り出すことができます。

基本となる構文は以下のとおりです。

select substring_index(カラム名,'.',-1) as 別名 from テーブル名;

SUBSTRING_INDEX()の第3引数に「-1」を指定すると、区切り文字（この場合はドット「.」）が最後に出現した位置より後ろの文字列を抽出できます。そのため、ドットを含むファイル名からも、最後の拡張子だけを正しく取り出せるのがポイントです。

サンプルテーブルの作成

それでは、実際に動作を確認してみましょう。まずはテーブルを作成します。テーブル作成用のクエリは次のとおりです。

mysql> create table getFileExtensionDemo
-> (
-> File_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> File_Name text
-> );
Query OK, 0 rows affected (0.53 sec)

テストデータの挿入

続いて、insertコマンドを使って複数のレコードを追加します。あえてドットを複数含むファイル名も登録し、動作を検証してみましょう。

mysql> insert into getFileExtensionDemo(File_Name) values('John.AllMySQLConcept.doc');
Query OK, 1 row affected (0.17 sec)
mysql> insert into getFileExtensionDemo(File_Name) values('Introductiontojava.txt');
Query OK, 1 row affected (0.17 sec)
mysql> insert into getFileExtensionDemo(File_Name) values('C and C++.AllDataStructureandAlgorithm.pdf');
Query OK, 1 row affected (0.14 sec)
mysql> insert into getFileExtensionDemo(File_Name) values('C.Users.Desktop.AllMySQLScript.sql');
Query OK, 1 row affected (0.39 sec)

登録データの確認

select文ですべてのレコードを表示して、登録内容を確認します。

mysql> select *from getFileExtensionDemo;

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

+---------+--------------------------------------------+
| File_Id | File_Name                                  |
+---------+--------------------------------------------+
| 1       | John.AllMySQLConcept.doc                   |
| 2       | Introductiontojava.txt                     |
| 3       | C and C++.AllDataStructureandAlgorithm.pdf |
| 4       | C.Users.Desktop.AllMySQLScript.sql         |
+---------+--------------------------------------------+
4 rows in set (0.00 sec)

拡張子だけを抽出するクエリ

ここで本題の、ファイルの拡張子を取得するクエリを実行します。

mysql> select substring_index(File_Name,'.',-1) as AllFileExtension from getFileExtensionDemo;

実行結果を見ると、ファイル名から拡張子のみが抽出されていることがわかります。

+------------------+
| AllFileExtension |
+------------------+
| doc              |
| txt              |
| pdf              |
| sql              |
+------------------+
4 rows in set (0.20 sec)

まとめ

SUBSTRING_INDEX()関数に負の値を指定することで、区切り文字の最後の出現位置以降の文字列を取得できます。「John.AllMySQLConcept.doc」や「C.Users.Desktop.AllMySQLScript.sql」のように途中にドットが含まれるファイル名でも、最後のドット以降の「doc」「sql」が正確に抽出されました。ファイル名から拡張子を取得したいケースでは、ぜひ活用してみてください。