SQLで拡張子付きファイル名から拡張子を取得

PostgreSQL SQL

「aaa.jpg」のようなファイル名が格納されているテーブルに対して、何の拡張子が使われてるか調査する機会があったため、備忘録として残します。 割と限定的な状況なので、役立つ機会はあまりないかもしれませんが...。あと地味にタイトルが難しい。タイトルが微妙なのも、限定的なのが原因ですね...。

準備

create table hoge_files (
    id bigserial,
    file_name character varying(1000),
    primary key (id)
)
;

insert into hoge_files (file_name) values
('aaa.jpg'),
('bbb.xlsx'),
('ccc.zip'),
('ddd.jpg')
;

取得クエリ

select
    distinct ext
from
    (
        select
            substring(full_file_name from length(file_name)+2) ext
        from
            (
                select
                    split_part(file_name, '.', 1) file_name,
                    file_name full_file_name
                from hoge_files
            ) files
    ) ext_files
order by ext
;

実行結果

 ext  
------
 jpg
 xlsx
 zip
(3 rows)

ちょっと特殊なパターンのとき

  • 拡張子がない
  • 拡張子を除いたファイル名にドットが含まれる
insert into hoge_files (file_name) values
('eee'),
('fff.fff.png')
;

実行結果

   ext   
---------
 
 fff.png
 jpg
 xlsx
 zip
(5 rows)

そもそもファイル名にドットなんて入れるなという話ですが。 こういうパターンはあまりないと思われますし、単に調査するだけなら問題ないでしょう。 どうしても拡張子のみ抽出したい場合、RubyなりPythonなりscriptで対処する方が良いと思います。

参考

[1] https://www.postgresql.org/docs/13/functions-string.html