(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).
