Geek Answers Handbook

Problem with Oracle / SQL ORDER BY Statement

I have the following content inside a varchar2 column:

   10.1.2.3
   10.2.3.4
   8.3.4.1
   8.3.2.1
   4.2.1.3
   4.3.2.1
   9.3.1.2

When I query the database, I need the result:

4....
8....
9....
10...

The NLS_SORT parameter is set to German, a simple " order by COLUMN DESC/ASC" does not work as excluded. He returns

10.....
8......
9......

any suggestions?

+5
source share
7 answers

Assuming this is 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)
+7
source

@RobVanWijk makes a corresponding comment:

you can argue that this should be instead of four-digit columns from 1 row.

, , . Oracle , . , . , UDT .

, , " ", . , , ...

...

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>
+2
  • , ;
  • FOR..LOOP [TABLE_NAME]% ROWTYPE ;
  • NUMBER ;
  • , ;
  • .

:

, , , , .

+1

. 1 8, 9. "", , , , , .

, 3 , 008.002.004.001, , . , , -.

0

10 10 .

10
4
8


4
8
10

( ), . SUBSTR INSTR . .

( , , IP-)

, , ( ORDER BY).

0

:

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

0

order by COLUMN

.

-1

More articles:


All Articles