Проблема с Oracle/SQL Оператор ORDER BY

@RobVanWijk делает уместный комментарий:

вы можете возразить, что это должно быть вместо этого хранится в виде четырех числовых столбцов из 1 струны.

Это классический случай, когда было бы здорово, если бы мы могли определять домены данных в наших схемах. Oracle не поддерживает это, но, честно говоря, ни один из других поставщиков СУБД. Тем не менее, мы можем использовать определяемые пользователем типы для создания сложных типов данных с прикрепленным поведением.Просто жаль, что синтаксис UDT такой неуклюжий.

Как бы то ни было, комментарий Роба напомнил мне, что некоторое время назад я создал Proof of Concept, используя именно этот домен. Я публикую это не как серьезное решение, а как индикатор того, насколько изящными могут быть вещи ....

Спецификация типа ...

create or replace type ip_address_t as object
    (octet1 number(3,0) 
     , octet2 number(3,0) 
     , octet3 number(3,0) 
     , octet4 number(3,0) 
     , constructor function ip_address_t 
            (octet1 number, octet2 number, octet3 number, octet4 number)
                        return self as result
     , member function to_string 
                        return varchar2
     , member function to_padded_string 
                        return varchar2
     , map member function sort_order return number)
/

... и тело ...

create or replace type body ip_address_t as 

    constructor function ip_address_t 
         (octet1 number, octet2 number, octet3 number, octet4 number)
                        return self as result
    is
    begin
        if ( octet1 is null or octet2 is null or octet3 is null or octet4 is null )
        then
            raise INVALID_NUMBER;
        else
            self.octet1 := octet1;
            self.octet2 := octet2;
            self.octet3 := octet3;
            self.octet4 := octet4;
        end if;
        return;
    end ip_address_t;

    member function to_string return varchar2
    is
    begin    
        return trim(to_char(self.octet1))||'.'||
               trim(to_char(self.octet2))||'.'||
               trim(to_char(self.octet3))||'.'||
               trim(to_char(self.octet4));
    end to_string;

    member function to_padded_string  return varchar2
    is
    begin    
        return lpad(trim(to_char(self.octet1)),3,'0')||'.'||
               lpad(trim(to_char(self.octet2)),3,'0')||'.'||
               lpad(trim(to_char(self.octet3)),3,'0')||'.'||
               lpad(trim(to_char(self.octet4)),3,'0');
    end to_padded_string;

    map member function sort_order return number
    is
    begin    
        return to_number(
                       lpad(trim(to_char(self.octet1)),3,'0')||
                       lpad(trim(to_char(self.octet2)),3,'0')||
                       lpad(trim(to_char(self.octet3)),3,'0')||
                       lpad(trim(to_char(self.octet4)),3,'0')
              );
     end sort_order;

end;
/

Я буду использовать этот тип для определения столбца в тестовой таблице, которую я заполню некоторыми тестовыми данными.

SQL> create table t23 (id number, domnain_name varchar2(128), ip_address ip_address_t)
  2  /

Table created.

SQL> insert into t23 values (1000, 'http://www.example.com', ip_address_t(8,1,3,0))
  2  /

1 row created.

SQL> insert into t23 values (800, 'http://www.example1.com', ip_address_t(9,1,2,0))
  2  /

1 row created.

SQL> insert into t23 values (1100, 'http://www.example2.com', ip_address_t(10,1,2,0))
  2  /

1 row created.

SQL> insert into t23 values (1103, 'http://www.example3.com', ip_address_t(10,1,25,0))
  2  /

1 row created.

SQL> insert into t23 values (1102, 'http://www.example4.com', ip_address_t(1,11,25,0))
  2  /

1 row created.

SQL> insert into t23 values (1101, 'http://www.example5.com', ip_address_t(11,1,25,0))
  2  /

1 row created.

SQL>

Всегда помните: при ссылке на атрибуты или методы столбца UDT мы должны использовать псевдоним таблицы :

SQL> select t.id
  2         , t.ip_address.to_string() as ip_address
  3  from t23 t
  4  order by t.ip_address.sort_order()
  5  /

        ID IP_ADDRESS
---------- ---------------
      1102 1.11.25.0
      1000 8.1.3.0
       800 9.1.2.0
      1100 10.1.2.0
      1103 10.1.25.0
      1101 11.1.25.0

SQL>

1. Создайте курсор, в котором вы будете выполнять цикл;
2. Используйте FOR..LOOP, используя [TABLE_NAME]%ROWTYPE в качестве информационного контейнера данных;
3. Разделите вашу строку и приведите к ЧИСЛУ первую полученную строку;
4. Сделайте то же самое для следующих строк, полученных в результате разделения снова и снова для каждого из чисел;
5. Вставьте отсортированный результат во временную таблицу и выберите результат из нее.

Иначе:

Вы также можете добавить новый столбец для сортировки записей в таблицу данных, что, по моему мнению, является лучшим подходом, если это возможно в вашей ситуации.

Это правильный порядок. 1 стоит перед 8, что предшествует 9. Поскольку это не «числа», это текст, вам, вероятно, придется либо преобразовать первую часть в число, либо заполнить их нулями при сортировке.

Например, если вы сделаете каждый сегмент шириной 3 цифры, скажем 008.002.004.001, вы можете отсортировать по алфавиту, и это будет работать. Вы можете отображать его, как хотите, но сортировать его придется по-другому.

10 в строке отличается от 10 в числе.

Строки будут сортироваться следующим образом

10
4
8

Числа будут сортироваться следующим образом

4
8
10

Вам придется взять часть строки (до первой точки), преобразовать ее в число и затем отсортировать. Для этого найдите функции SUBSTR и INSTR. Затем найдите функцию для преобразования строки в целое число.

Вам придется сделать это для каждого октета (я говорю так только потому, что показанные вами числа похожи на IP-адрес)

Итак, в основном вам нужно разделить данные на четыре целочисленных столбца и затем отсортировать по ним (или просто сделать разделение в предложении ORDER BY).

Используйте регулярные выражения:

order by lpad (ltrim (regexp_substr (COLUMN, '(.? [^.] ) | ([^.] ;?)', 1,1) , '.'), 3, '0') , lpad (ltrim (regexp_substr (COLUMN, '(.? [^.] ) | ([^.] ;?)', 1,2), '.'), 3, '0' ) , lpad (ltrim (regexp_substr (COLUMN, '(.? [^.] ) | ([^.] ;?)', 1,3), '.'), 3, '0' ) , lpad (ltrim (regexp_substr (COLUMN, '(.? [^.] ) | ([^.] ;?)', 1,4), '.'), 3, '0' )

должно быть

order by COLUMN

По умолчанию только по возрастанию.