Сортировка по текстовому полю содержащему комбинацию букв и цифр

    Сортировка результата SQL запроса – это то, с чем программисты сталкиваются постоянно. Обычно СУБД при сортировке текстовых полей для сравнения строк использует лексикографический порядок. Согласно Википедии, он означает, что слово X предшествует слову Y (X < Y), если:

• либо слово X является началом слова Y (например, "МАТЕМАТИК" < "МАТЕМАТИКА");
• либо первые m символов этих слов совпадают, а m+1-й символ слова X меньше m+1-го символа слова Y (например, "АБАК" < "АБРАКАДАБРА", так как первые две буквы у этих слов совпадают, а третья буква у первого слова меньше, чем у второго).

Этот способ сортировки строк подходит для большинства случаев. Проблемы могут возникнуть если строки содержат в себе комбинацию букв и цифр, а пользователи захотят видеть сортировку в "естественном порядке". Естественный порядок сортировки – это упорядочение строк в лексикографическом порядке, за исключением того, что многозначные числа обрабатываются атомарно (то есть, как один символ).

Лексикографический порядок	Естественный порядок
a12	a2a
a22	a2b
a2a	a3a2
a2b	a3a2a
a30z	a12
a3a2	a22
a3a2a	a30z

    Недавно аналитик спросила меня: "А можно сделать так, чтобы документы сортировались по номеру "правильно"?". Под "правильно" подразумевался естественный порядок сортировки. Я ответил "Не проблема" и отдал разработчикам, функцию, которую давно своровал где-то на просторах интернета и немного упростил "обработав напильником":

create function sort_string(@SortStr varchar(4000), @NumberLength int)
  returns varchar(4000)
as
begin
  declare @SortedStr varchar(4000) = @SortStr,
          @StartIndex int = PATINDEX('%[0-9]%', @SortStr),
          @EndIndex int = 0,
          @PadLength int,
          @TotalPadLength int = 0;

  while @StartIndex <> 0
    begin
      set @StartIndex = @StartIndex + @EndIndex;
      set @EndIndex = @StartIndex;

      while PATINDEX('[0-9]', SUBSTRING(@SortStr, @EndIndex, 1)) = 1
        set @EndIndex = @EndIndex + 1;
      set @PadLength = @NumberLength - (@EndIndex - @StartIndex);

      if @PadLength > 0
        begin
          set @SortedStr = STUFF(@SortedStr, 
                                 @TotalPadLength + @StartIndex, 
                                 0,
                                 REPLICATE('0', @PadLength));
          set @TotalPadLength = @TotalPadLength + @PadLength;
        end;

      set @EndIndex = @EndIndex - 1;
      set @StartIndex = PATINDEX('%[0-9]%', 
                                 RIGHT(@SortStr, LEN(@SortStr) - @EndIndex));
    end;

  return @SortedStr;
end

Эта функция формирует строку, по которой MS SQL Server сделает "правильную" сортировку по текстовому полю. Как она работает? Предположим, что наши строки содержат только цифры: "12", "2", "22", "3". Если отсортировать их в лексикографическом порядке, то получим: "12", "2", "22", "3". Что бы отсортировать эти строки в порядке, который соответствует сортировке чисел, необходимо дополнить их слева до длины поля нулями. Допустим, длина нашего текстового поля будет 5 символов (@NumberLength = 5). Дополним строки нулями до длины в 5 символов и отсортируем: "00002", "00003", "00012", "00022". Теперь представим, что эти строки с цифрами и есть те самые многозначные числа, которые необходимо обработать атомарно. Получаем простой алгоритм формирования строки для сортировки: делим строку на символьные и числовые подстроки, числовые подстроки дополняем слева нулями до максимальной длины (@NumberLength) и объединяем подстроки в одну строку. В результате применения этого алгоритма набор строк для сортировки моего примера будет таким: "a00002a", "a00002b", "a00003a00002", "a00003a00002a", "a00012", "a00022", "a00030z".

select doc_index, doc_title
  from table
order by dbo.sort_string(doc_index, 5)

    – А для Oracle у тебя такая функция есть?
    – Нет, но я могу ее портировать.
При портировании функции на PL/SQL, я реализовал описанный алгоритм с использованием регулярных выражений:

create or replace function sort_string(SortStr varchar2, 
                                       NumberLength pls_integer)
  return varchar2
is
  SortedStr varchar2(4000);
begin
  select LISTAGG(case 
                   when REGEXP_INSTR(rs, '\d') > 0 
                     then LPAD(rs, NumberLength, '0')
                     else rs 
                   end) within group (order by order_num)
  into SortedStr
  from (select LEVEL as order_num, 
               REGEXP_SUBSTR(SortStr, '\d+|\D+', 1, LEVEL) as rs 
          from dual
        connect by REGEXP_INSTR(SortStr, '\d+|\D+', 1, LEVEL) > 0) t;

  return SortedStr;
end;

Новая версия нашего продукта кроме MS SQL Server и Oracle поддерживает еще и PostgreSQL. Поэтому под него пришлось написать третий вариант функции:

CREATE OR REPLACE FUNCTION sort_string(SortStr varchar(4000), NumberLength int)
  RETURNS varchar(4000)
AS
$BODY$
  select STRING_AGG(case 
                      when rm[1]='' then '' 
                      else LPAD(rm[1], NumberLength, '0')
                    end || rm[2], '')
    from (select REGEXP_MATCHES(SortStr, '(^|\d+)(\D+|$)', 'g') rm) t;
$BODY$
LANGUAGE SQL STABLE;

    P.S. В результате в таблицу с документами добавили поле, в которое триггер сохраняет строку для сортировки.