Here's one way to do it:
create table ip ( ip_address varchar2(15) );
insert into ip values('10.0.112.1');
insert into ip values('10.0.113.1');
insert into ip values('10.0.113.2');
insert into ip values('10.0.12.1');
insert into ip values('10.0.78.1');
commit;
create or replace function ipsort ( ip_address_in varchar2 )
return number
is
nip number;
o1 varchar2(3);
o2 varchar2(3);
o3 varchar2(3);
o4 varchar2(3);
begin
/* this function assumes a complete ip address of nnn.nnn.nnn.nnn
though the number of digits in each octet does not need to be
three, just >= 1
*/
o1 := substr(ip_address_in,1,instr(ip_address_in,'.',1)-1);
o2 := substr(ip_address_in,instr(ip_address_in,'.',1,1)+1,
(instr(ip_address_in,'.',1,2)-1) - (instr(ip_address_in,'.',1,1)));
o3 := substr(ip_address_in,instr(ip_address_in,'.',1,2)+1,
(instr(ip_address_in,'.',1,3)-1) - (instr(ip_address_in,'.',1,2)));
o4 := substr(ip_address_in,instr(ip_address_in,'.',1,3)+1);
nip := (to_number(o1) * 16 * power(2,20)) + ( to_number(o2) * 64 *
power(2,10) ) + ( to_number(o3) * 256 ) + to_number(o4);
return nip;
end;
/
select ip_address
from ip
order by ipsort(ip_address)
/
IP_ADDRESS
---------------
10.0.12.1
10.0.78.1
10.0.112.1
10.0.113.1
10.0.113.2
5 rows selected.
Jared
Ray Stell <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
03/25/2003 09:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: sort ip addresses
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
===============================================================
Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC 28^D
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ray Stell
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
