> -----Original Message-----
> From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]
>
> SELECT col1 FROM
<data snipped>
> ORDER BY TO_NUMBER(REPLACE (col1,'.','0'))
>
The above works for the given test data but doesn't work in the general case,
for example if '192.168.10.10' and '192.168.1.100' are in the data set.
There may be a better way to do it but this seems to work (if a user-defined
function is an acceptable solution). In a production environment, as opposed
to a mailing list hack, I'd add error checking and data checks.
create or replace function iptonumber(ip_in varchar2)
return number
deterministic
is
ip_local varchar2(15) := ip_in;
ip_out number := 0;
octet number := 0;
multiplier number := 1000;
begin
while (length(ip_local) > 0) loop
while substr(ip_local, 1, 1) != '.' loop
octet := octet * 10 + to_number(substr(ip_local, 1, 1));
ip_local := substr(ip_local, 2);
end loop;
ip_local := substr(ip_local, 2);
ip_out := ip_out * 1000 + octet;
octet := 0;
end loop;
return ip_out;
end;
/
SQL>select ip from ip_test order by iptonumber(ip);
IP
--------------------
10.0.112.1
10.0.113.1
192.168.0.10
192.168.0.101
192.168.1.100
192.168.10.10
6 rows selected.
SQL>select ip from ip_test order by to_number(replace(ip, '.', '0'));
IP
--------------------
10.0.112.1
10.0.113.1
192.168.0.10
192.168.0.101
192.168.10.10
192.168.1.100
6 rows selected.
LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged.
It is intended for the addressee(s) only. Access to this e-mail by anyone else is
unauthorized. If you are not an addressee, any disclosure or copying of the contents
or any action taken (or not taken) in reliance on it is unauthorized and may be
unlawful. If you are not an addressee, please inform the sender immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sarnowski, Chris
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).
