RE: sort ip addresses

Tue, 25 Mar 2003 12:45:19 -0800

Title: RE: sort ip addresses
Jacques,
 
you are correct - and go ahead and be picky.  my first solution was wrong as Ray pointed out to me.  looks like the pattern matching example you gave works.  this also works:
 
 
SELECT col1 FROM
(SELECT '10.0.113.1' col1
FROM dual
UNION
SELECT '10.0.112.1'
FROM dual
UNION
SELECT '10.99.114.1'
FROM dual
UNION
SELECT '10.1.90.1'
FROM dual
UNION
SELECT '128.173.10.300'
FROM dual
UNION
SELECT '128.173.10.1'
FROM dual
UNION
SELECT '99.200.90.255'
FROM dual
UNION
SELECT '10.0.80.1'
FROM dual
UNION
SELECT '188.82.80.1'
FROM dual
UNION
SELECT '128.173.10.255'
FROM dual
)
ORDER BY TO_NUMBER(SUBSTR(col1,1,INSTR(col1,'.',1))),
                  TO_NUMBER(SUBSTR(col1,INSTR(col1,'.',1)+1,INSTR(col1,'.',2))),
                  TO_NUMBER(SUBSTR(col1,INSTR(col1,'.',2)+1,INSTR(col1,'.',3)))
 
 

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 2:44 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: RE: sort ip addresses

(see comments below)

> -----Original Message-----
> From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
>
> SELECT col1 FROM
> (SELECT '10.0.112.1' col1
>  FROM dual
>  UNION
>  SELECT '10.0.113.1'
>  FROM dual
> UNION
> SELECT '10.0.114.1'
> FROM dual
> UNION
> SELECT '10.0.90.1'
> FROM dual
> UNION
> SELECT '10.0.80.1'
> FROM dual)
> ORDER BY TO_NUMBER(REPLACE (col1,'.','0'))
>
>
> -----Original Message-----
> From: Ray Stell [mailto:[EMAIL PROTECTED]]
>
> Need a trick to sort ip addresses into numerical order.  I have them
> stored as varchar2.  If I select without a sort I get rows
> in order of their character value:
>
> 10.0.112.1
> 10.0.113.1
> 10.0.113.2
> 10.0.12.1
> 10.0.78.1
>
> I'd like to order them numerically within the octets:
>
> 10.0.12.1
> 10.0.78.1
> 10.0.112.1
> 10.0.113.1
> 10.0.113.2


I hate to be picky, Thomas, but your solution assumes (for example) that all the first quads have the same number of digits. Included below is an example where your proposal would return incorrect results. Below that I have another proposed solution.

SQL> SELECT col1 FROM
  2  (SELECT '10.0.112.1' col1
  3   FROM dual
  4   UNION
  5   SELECT '10.0.113.1'
  6   FROM dual
  7  UNION
  8  SELECT '10.0.114.1'
  9  FROM dual
 10  UNION
 11  SELECT '10.0.90.1'
 12  FROM dual
 13  UNION
 14  SELECT '10.0.80.1'
 15  FROM dual
 16  union select '192.2.2.1' from dual)
 17  ORDER BY TO_NUMBER(REPLACE (col1,'.','0')) ;

COL1
----------
10.0.80.1
10.0.90.1
192.2.2.1
10.0.112.1
10.0.113.1
10.0.114.1

SQL> select
  2     to_number (substr (a.col1, 1, owa_pattern.amatch (a.col1, 1, '\d+') - 1))
  3        as quad1,
  4     to_number (substr (a.col1,
  5                        owa_pattern.amatch (a.col1, 1, '\d+') + 1,
  6                        owa_pattern.amatch (a.col1, 1, '\d+\.\d+')
  7                           - owa_pattern.amatch (a.col1, 1, '\d+') - 1
  8                       )
  9               ) as quad2,
 10     to_number (substr (a.col1,
 11                        owa_pattern.amatch (a.col1, 1, '\d+\.\d+') + 1,
 12                        owa_pattern.amatch (a.col1, 1, '\d+\.\d+\.\d+')
 13                           - owa_pattern.amatch (a.col1, 1, '\d+\.\d+') - 1
 14                       )
 15               ) as quad3,
 16     to_number (substr (a.col1,
 17                        owa_pattern.amatch (a.col1, 1, '\d+\.\d+\.\d+') + 1
 18                       )
 19               ) as quad4
 20  from
 21     ( select '10.0.112.1' as col1 from dual
 22      union all
 23       select '10.0.113.1' as col1 from dual
 24      union all
 25       select '10.0.114.1' as col1 from dual
 26      union all
 27       select '10.0.90.1' as col1 from dual
 28      union all
 29       select '10.0.80.1' as col1 from dual
 30      union all
 31       select '192.2.2.1' as col1 from dual
 32     ) a
 33  order by quad1, quad2, quad3, quad4 ;

    QUAD1     QUAD2     QUAD3     QUAD4
--------- --------- --------- ---------
       10         0        80         1
       10         0        90         1
       10         0       112         1
       10         0       113         1
       10         0       114         1
      192         2         2         1
6 ligne(s) selectionnee(s).

Reply via email to