Получение расширения файла в SQL-запросе

Фантазия авторов PostgreSQL поражает. Его разнообразные синтаксические конструкции почти всегда позволяют решить задачу несколькими способами. Например, рассмотрим получение расширения файла из его имени. В отличии от Oracle и MS SQL Server, для PostgreSQL я насчитал 4 варианта.

Создадим тестовую таблицу и добавим туда записи с различными значениями имени файла:

CREATE TABLE test (name TEXT);
INSERT INTO test (name) 
  VALUES ('обычное расширение.xyz'), 
         ('расширение с пробелом.x y'), 
         ('имя файла с точками.x.y.z'), 
         (NULL), 
         ('без расширения'), 
         ('.без имени'), 
         ('пустое расширение.');

Вариант 1. Я бы назвал его "стандартным", т.к. его можно подогнать под любую СУБД:

select name, 
       case 
         when POSITION('.' in name) > 0 then 
              LOWER(RIGHT(name, POSITION('.' in REVERSE(name)) - 1)) 
         else '' 
       end as ext 
  from test;

Меняйте в этом запросе имена строковых функций и он подойдет для нужной вам СУБД. Например, поменяем POSITION на CHARINDEX и запустим запрос на MS SQL Server:

select name, 
       case 
         when CHARINDEX('.', name) > 0 then 
              LOWER(RIGHT(name, CHARINDEX('.', REVERSE(name)) - 1)) 
         else '' 
       end as ext
  from test

Результат выполнения запроса совпадает с PostgreSQL на 100%:

Вариант 2. Для тех, кто любит регулярные выражения PostgreSQL - это просто рай:

select name, 
       COALESCE(LOWER(SUBSTRING(name from '(?<=\.)[^./\\]+$')), '') AS ext
  from test;

Вариант 3. PostgreSQL отлично работает с массивами. Поэтому, используя точку в качестве разделителя, разобьем имя файла на элементы массива и возьмем его последний элемент:

select name, 
       case 
         when POSITION('.' in name) > 0 then 
              LOWER((STRING_TO_ARRAY(name, '.'))[ARRAY_UPPER(STRING_TO_ARRAY(name, '.'), 1)]) 
         else '' 
       end as ext1,
       case 
         when POSITION('.' in name) > 0 then 
              LOWER((STRING_TO_ARRAY(name, '.'))[ARRAY_LENGTH(STRING_TO_ARRAY(name, '.'), 1)]) 
         else '' 
       end as ext2
  from test;

Посчитаем этот вариант за один, хотя тут их два.

Вариант 4. Этот вариант рассматриваю последним, т.к. он работает только с 14-й версии PostgreSQL. Функция SPLIT_PART разделяет строку на части и возвращает ее заданную часть. Начиная с PostgreSQL 14 номер части строки может быть отрицательным, что позвонят указывать его с конца. Используя точку в качестве разделителя, разделим имя файла на части и возьмем его последнюю часть:

select name, 
       case 
         when POSITION('.' in name) > 0 then LOWER(SPLIT_PART(name, '.', -1)) 
         else '' 
       end as ext 
  from test;

Не удивлюсь, если у кого то есть еще варианты.