There's probably a neater solution, but here's a quick and dirty function
that zero-pads each octet & returns a value you should be able to use in an
ORDER BY:
----------------------------------------------------
create or replace function OrderIP(p_IP IN VARCHAR2) return VARCHAR2 is
v_octet number ;
v_start number ;
v_end number ;
v_currlen number ;
v_padded varchar2(15) ;
c_octet_length constant number := 3 ;
begin
v_octet := 1 ;
v_start := 1 ;
v_end := instr(p_IP, '.', v_octet) ;
while v_end > 0 loop
v_currlen := (v_end - v_start) ;
v_padded := v_padded || lpad(substr(p_IP, v_start, v_currlen),
c_octet_length, '0') ;
v_start := v_end + 1 ;
v_octet := v_octet + 1 ;
v_end := instr(p_IP, '.', v_octet) ;
end loop ;
-- Finally, get the last octet.
v_padded := v_padded || lpad(substr(p_IP, v_start), c_octet_length, '0')
;
return v_padded ;
end OrderIP ;
----------------------------------------------------
HTH,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
Sent: Tuesday, March 25, 2003 9:54 AM
To: Multiple recipients of list ORACLE-L
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: Pardee, Roy E
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).
