Problem with Oracle/SQL ORDER BY Statement

Assuming it's an IP address

SELECT col  FROM table ORDER BY (regexp_substr(col, '[^.]+', 1, 1) * 256  * 256  * 256 ) + (regexp_substr(col, '[^.]+', 1, 2) * 256 * 256) + (regexp_substr(col, '[^.]+', 1, 3) * 256 )+ regexp_substr(col, '[^.]+', 1, 4)

@RobVanWijk makes a pertinent comment:

you could argue that those should be stored as four numeric columns instead of 1 string.

This is a classic case where it would be cool if we could define data domains in our schemas. Oracle doesn't support this, but to be fair nor do any of the other DBMS vendors. Still, we can employ User-defined types to build complex datatypes with attached behaviour. It's just a shame that the UDT syntax is so clunky.

Anyway, Rob's comment has reminded me that I knocked up a Proof of Concept using this very domain a while back. I am posting it not as a serious solution, but as an indicator of how neat things could be....

The type specification ...

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)/

... and body ...

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;/

I will use this type to define a column in a test table which I will populate with some test data.

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>

Always remember: when referencing the attributes or methods of a UDT column we have to use a table alias:

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.0SQL>

1. Create a cursor with which you will loop into;
2. Use a FOR..LOOP while using a [TABLE_NAME]%ROWTYPE as information data container;
3. Split your string and cast to NUMBER the first string obtained;
4. Do the same for the following strings gotten from splitting over and over again for each of the numbers;
5. Insert the sorted result into a temporary table and select the result from it.

Otherwise:

You also could add a new column to sort records with to your data table, which according to me is the better approach, if doable into your situation.