На входе подается строка с произвольным набором символов, включающих в себя английские,
русские строчные и заглавные буквы и цифры. Я записываю в секции 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
Сначала сортируемая строка и шаблон разбиваются на символы. При этом символы нумеруются
и для каждого символа шаблона понятен его порядковый номер. После символы соединяются
и сортируются по порядку шедшему в шаблоне с последующим объединением в одну строку.
