Произвольный порядок сортировки символов из константы в pl/sql

#sql #oracle #plsql

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

DECLARE
  str CONSTANT varchar2(32767) := 'CBEdfa092борДЖЭ';


После сортировки первыми должны быть сначала строчные русские буквы, потом заглавные
русские, цифры, английские строчные, английские заглавные.

Пример вывода по передаваемой константе: борДЖЭ029adfBCE.

Можно ли сделать сортировку, использую какую-либо готовую функцию, где можно будет
передать шаблон и она по нему отсортирует(что-то наподобие формата для дат)?

Пишу анонимный блок в SQL Developer
    

Ответы

Ответ 1

with TRANS(start_, end_, ord) as(
 select 'a', 'z',1 from DUAL union all
 select 'A', 'Z',2 from DUAL union all
 select '0', '9',3 from DUAL union all
 select 'А', 'Я',4 from DUAL union all
 select 'Ё', 'Ё',4 from DUAL union all
 select 'а', 'я',5 from DUAL union all
 select 'ё', 'ё',5 from DUAL
), Src(str) as(
 select 'CBEdfa092борДЖЭёе' from DUAL
)
select listagg(ch) within group (order by rn)
  from (
    select ch, row_number() over(order by ord, ch) rn
      from ( select substr(str,level,1) ch
               from Src
            connect by substr(str,level,1) is not null
           ) left join TRANS on ch between start_ and end_
     order by ord, ch
  )


Остается только в Src подставить переменную с входной строкой, добавить into что
бы поместить результат так же в переменную. И в таблице TRANS задать нужные приоритеты
для диапазонов символов.

P.S. дополнительный уровень с нумерацией и использованию в listagg номера сделан
из за того, что listagg не соблюдает нормальный, алфавитный порядок сортировки буквы
ё (в отличие от обычного order by).

P.P.S. Внимание Выяснено экспериментальным путем (совместно с @Dmitry), что в зависимости
от региональных настроек (видимо) правильный порядок буквы ё может быть правильным
либо у listagg, либо у order by. Поэтому требуется проверка работы этих букв на целевой
системе и применение в listagg сортировки либо в rn, либо по ord, ch в зависимости
от того, что даст лучший результат.


Ответ 2

Если я правильно понял вашу мысль, можно сделать как-то так. Допустим, есть источник:

with source as (
    select 'abc' a from dual union all
    select 'def' a from dual union all
    select 'hij' a from dual)
select *
  from source;


Надо сделать кастомную сотрировку, чтобы буква d шла первой, а a - второй. Тогда:

with source as (
    select 'abc' a from dual union all
    select 'def' a from dual union all
    select 'hij' a from dual)
select a, sort_column
  from (select a, translate(a, 'adh', 'dah') sort_column
          from source)
 order by sort_column;


С помощью translate заменяем символы, третий параметр задает необходимую сортировку.
Далее сортируем по результату транслейта. Результат:

A   SORT_COLUMN
--- -----------
def aef         
abc dbc         
hij hij         


UPD.
Немного о принципе формирования строки для функции translate. У оракла есть какая-то
встроенная последовательность. В данном случае сортируется просто по возрастанию ASCII
кодов. В функцию translate мы передаем первым параметром исходную строку, вторым -
существующий порядок сортировки, третьим - преобразование к желаемому порядку. Тут
можно сломать мозг, так что осторожнее.
Ваша исходная строка, существующий порядок сортировки, строка преобразования:

0 2 9 B C E a d f Д Ж Э б о р
б о р Д Ж Э 0 2 9 a d f B C E
a d f б о р Д Ж Э B C E 0 2 9 <-- это пойдет в функцию translate


Вам надо, чтобы сначала шла буква б, потом о, потом р и так далее. В существующем
порядке сортировки первым идет 0 (вторая строка). Следовательно, под б в первой строке
пишем 0 в третьей. Потом о - под о пишем 2, и так далее. Для полного алфавита, соответственно,
будет:

существующий порядок 0 1 2 3 4 5 6 7 8 9 A B C ... a b c ... А Б В ... a б в ... э ю я
строка замены        а б в г д е ё ж з и к л м ................................. X Y Z


Принцип, надеюсь, понятен.

Далее все решается в один запрос:

select listagg(letters) within group (order by srt) 
  from (select letters, translate(letters, '029BCEadfДЖЭбор' /*существующий порядок*/, 
                                           'adfборДЖЭBCE029' /*строка замены*/) srt
          from (select substr('CBEdfa092борДЖЭ', level, 1) letters 
                  from dual connect by level <= length('CBEdfa092борДЖЭ'))
  )

RES 
----------------
борДЖЭ029adfBCE


Ответ 3

Написал запрос использующий рекурсивный with. Никаких дополнительных таблиц не надо.
Можно просто передать строку и шаблон. Шаблон сортировки должен содержать все символы,
которые могут встретится в сортируемой строке.
У запроса 2 параметра: :str - сортируемая строка, :mask - шаблон сортировки.

with src(symbol, tail, lvl) as( -- разобьем строку на символы
  select substr(:str, 1, 1) as symbol, 
         substr(:str, 2) as tail, 
         1 as lvl 
  from   dual
  union  all
  select substr(tail, 1, 1), 
         substr(tail, 2) as tail, 
         lvl + 1 as lvl 
  from   src
  where  length(tail) > 0
)
,mask(symbol, tail, lvl) as( -- разобьем маску на символы.
  select substr(:mask, 1, 1) as symbol, 
         substr(:mask, 2) as tail, 
         1 as lvl 
  from   dual
  union  all
  select substr(tail, 1, 1), 
         substr(tail, 2) as tail, 
         lvl + 1 as lvl 
  from   mask
  where  length(tail) > 0
)
select listagg(symbol) within group (order by lvl) 
from   (
       select m.symbol, m.lvl
       from   mask m 
       inner  join src s
       on     m.symbol = s.symbol
)


На вход для сортировки подал строку -CBEdfa092борДЖЕ, шаблон сортировки - абвгдорАБВГД0123456789abcdefABCDEF
результат:


  борДЖЕ029adfBCE


Сначала сортируемая строка и шаблон разбиваются на символы. При этом символы нумеруются
и для каждого символа шаблона понятен его порядковый номер. После символы соединяются
и сортируются по порядку шедшему в шаблоне с последующим объединением в одну строку.