The input is a string with an arbitrary set of characters, including English, Russian lowercase and uppercase letters and numbers. I write in the DECLARE section like this:

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

After sorting, the first must first be lowercase Russian letters, then uppercase Russian, numbers, English lowercase, English uppercase.

An example of the output on the transmitted constant: борДЖЭ029adfBCE .

Is it possible to make sorting using any ready-made function, where it will be possible to transfer a template and it will sort by it (something like a format for dates)?

I am writing an anonymous block in SQL Developer

  • A constant with characters is defined like this: DECLARE str CONSTANT varchar2(32767) := 'CBEdfa092борДЖЭ'; . I need to sort it by the "ЯЯ, 0..9, zZ" template in the anonymous block. - user214690
  • you would show an example of input data and the result you want to see - Viktorov
  • Ok, I'll update the question now. - user214690
  • It seems that there is no ready-made function for Orakla. It seems that the problem can be solved through recursion or trees. - Viktorov

3 answers 3

 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 )

It only remains to substitute a variable with an input string in Src, add into to place the result in the variable as well. And in the TRANS table, set the necessary priorities for character ranges.

PS An additional level with numbering and use in listagg numbers is made due to the fact that listagg does not follow the normal, alphabetical order of sorting the letter ё (as opposed to the usual order by).

PPS Warning It was found experimentally (in conjunction with @Dmitry) that depending on the regional settings (apparently) the correct order of the letter ё may be correct either in listagg or in order by. Therefore, it is required to check the operation of these letters on the target system and use sorting in listagg either in rn or by ord, ch , depending on what gives the best result.

  • Perhaps your answer is most suitable for solving the problem. Thanks again @Mike :). And thank you all for so many decisions (it will be something at your leisure to sort out). - user214690
  • select listagg(ch) within group (order by rn) displays correctly - user214690

If I understand your idea correctly, you can do something like this. Suppose there is a source:

 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;

It is necessary to make a custom erase, so that the letter d goes first, and a - the second. Then:

 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;

With the help of translate replace characters, the third parameter sets the necessary sorting. Next, sort by the result of the translation. Result:

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

UPD.
A little bit about the principle of forming a string for the translate function. There is some built-in sequence. In this case, it is sorted simply in ascending ASCII codes. In the translate function, we pass the first string as the first parameter, the second as the existing sorting order, and the third as the conversion to the desired order. Here you can break the brain, so be careful. Your source line, existing sort order, conversion line:

 0 2 9 BCE adf Д Ж Э б о р б о р Д Ж Э 0 2 9 adf BCE adf б о р Д Ж Э BCE 0 2 9 <-- это пойдет в функцию translate

You need to first go letter б , then о , then р and so on. In the existing sort order, the first is 0 (second line). Therefore, under б in the first line we write 0 in the third. Then о - under о we write 2 , and so on. For the complete alphabet, respectively, will be:

 существующий порядок 0 1 2 3 4 5 6 7 8 9 ABC ... abc ... А Б В ... a б в ... э ю я строка замены а б в г д е ё ж з и к л м ................................. XYZ

The principle, I hope, is clear.

Then everything is solved in one request:

 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
  • I updated the question, look. I'm afraid this solution does not fit - user214690
  • @Fasta Suitable, but it needs to be finalized with a file. More precisely, to explain in more detail exactly how it works. I will finish it in 20 minutes - Dmitriy
  • First, split the line through APEX_STRING.SPLIT() and sorted by bubble. I thought to scatter the arrays in separate characters and numbers and then assemble the string in the right order. But it seemed too short of a decision and decided to ask if there is a ready function in pl / sql for such cases :) - user214690
  • In the course of ё , so I dropped this venture with a bubble and began to ask here. - user214690
  • @Fasta Hold, wrote. - Dmitriy

Wrote a query using recursive with. No additional tables are needed. You can simply pass the string and pattern. The sorting pattern must contain all characters that may occur in the string to be sorted. The query has 2 parameters:: :str - the string to be sorted,: :mask - the sorting pattern.

 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 )

At the input for sorting filed a string - CBEdfa092борДЖЕ , the sorting pattern - абвгдорАБВГД0123456789abcdefABCDEF result:

borJE029adfBCE

First, the string to be sorted and the pattern are broken into characters. In this case, the characters are numbered and its serial number is clear for each character of the pattern. After the characters are joined and sorted in order, followed in a pattern and then combined into one line.

  • However, your approach turned out to be the most resistant to the hawks of the usual order by with the letters ё , where you write, you will not need to get and think about it :) - Mike
  • However, in my approach, it will not be possible to specify ranges as you and the entire sorting line must be explicitly written :) - Viktorov
  • Well, yes, I’m generally very lazy, I can reinvent the algorithm for two hours, which will save me from 2 minute, routine work :) By the way, I just wanted to see how oracle will cope with "й" (not to be confused with "" ":)) that worrying about the fact that he incorrectly sorts it and doesn't even break it up into parts, I did not manage to drive it into the editor in the form of select 'й' from DUAL and get it in its original form :) (the sentence must be copied, in it two Code th) - Mike