Re: sort ip addresses
On Tue, Mar 25, 2003 at 04:58:53PM -0800, Jacques Kilchoer wrote: Every Dog Has His Day! And today's mine. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Jacques, this indeed an exquisite use of owa_pattern! -- Thanks everyone, for your suggestions, very helpful. === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^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).
RE: sort ip addresses
Ray, try this: 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')) Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 25, 2003 12:54 PM 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 KE4TJC28^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: Mercadante, Thomas F 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).
RE: sort ip addresses
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 KE4TJC28^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).
Re: sort ip addresses
This could be ugly, but you could use combinations of ltrim, rtrim, substr, and to_number to select and sort. Another idea would be to store your IP's in 4 number fields ip_a, ip_b, ip_c, ip_d and then do your select using: select ip_a||'.'||ip_b||'.'||ip_c||'.'||ip_d from ip_table order by ip_a, ip_b, ip_c, ip_d Again, this isn't pretty, but is easier than the first idea. The problem with storing them as text is that 70 comes after 100 because text sorts by a character as it comes to it as if it were a letter. The second one will output IP's in a workable format if you are spooling output for pinging, ftp, or some other access. Rodd On Tue, 2003-03-25 at 11:54, Ray Stell wrote: 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 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Rodd Holman [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodd Holman 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).
Re: sort ip addresses
Ray Stell wrote: 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 KE4TJC28^D -- Ray, This works if you do not have weird NLS settings : 1 select address from ip 2 order by to_number(substr(address, 1, instr(address, '.', 1, 2) - 1)), 3* to_number(substr(address, instr(address, '.', 1, 2) + 1)) SQL / ADDRESS 10.0.12.1 10.0.78.1 10.0.112.1 10.0.113.1 10.0.113.2 -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
RE: sort ip addresses
I've had this around for a while ... and just dug it out of my scripts and tried it again . Works for sorting 4 octet ips. You can always add sections on if they are ipv6. Ugly looking ... but it works. Note, you can probably clean up the math a bit ... i.e. combine plusses into +2, +3 etc. I just never did because it worked. select ip from iptable orderby lpad(substr(ip,1,instr(ip,'.')-1),3,'0')|| lpad(substr(ip, instr(ip,'.')+1,(instr(ip,'.',instr(ip,'.')+1)-1)-instr(ip,'.')),3,'0')|| lpad(substr(ip, instr(ip,'.',instr(ip,'.')+1)+1,(instr(ip,'.',instr(ip,'.',instr(ip,'.')+1)+ 1))-instr(ip,'.',instr(ip,'.')+1)-1),3,'0')|| lpad(substr(ip, instr(ip,'.',instr(ip,'.',instr(ip,'.')+1)+1)+1),3,'0') -Original Message- Sent: Tuesday, March 25, 2003 11: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 KE4TJC28^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: Kevin Lange 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).
RE: sort ip addresses
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 PMTo: '[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).
Re: sort ip addresses
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 KE4TJC28^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).
RE: sort ip addresses
Title: 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).
RE: sort ip addresses
-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; / SQLselect 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. SQLselect 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).
Re: sort ip addresses
Ooops. Disregard previous post, had a tiresome day. Ray Stell wrote: 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 KE4TJC28^D -- My previous post, like some other suggestions (translate, etc.) was too clever by half. This is uglier but works better. SQL get iptest 1 select address 2 from ip 3 order by to_number(substr(address, 1, instr(address, '.', 1, 1) - 1)), 4 to_number(substr(address, instr(address, '.', 1, 1) + 1, 5 instr(address, '.', 1, 2) 6 - instr(address, '.', 1, 1))), 7 to_number(substr(address, instr(address, '.', 1, 2) + 1, 8 instr(address, '.', 1, 3) 9 - instr(address, '.', 1, 2))), 10* to_number(substr(address, instr(address, '.', 1, 3) + 1)) SQL / ADDRESS 10.0.12.1 10.0.78.1 10.0.112.1 10.0.113.1 10.0.113.2 -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
RE: sort ip addresses
-Original Message-
Need a trick to sort ip addresses into numerical order.
Something a little different (not tested, so might need a little
adjustment).
-- go.ksh -
#!/usr/bin/ksh
{
sqlplus -s -XXX
/user/password
set feedback off heading off trimspool on
set pages 0
select ip_address from the_table;
} | /usr/bin/sort -nt . +1 +2 +3 +4
You ... ummm ... didn't NEED this to be entirely within a database session
did you?
And the sort options use the letters nt. I hope that is OK.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephen Lee
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).
RE: sort ip addresses
OMG! Jacques gave a perfect example using pattern matching, and Jared
didn't agree that it was the cat's meow to do it that way! :)
-Original Message-
Sent: Tuesday, March 25, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L
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 KE4TJC28^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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
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).
RE: sort ip addresses
Title: RE: sort ip addresses When I saw Jared's e-mail, it was like a dagger through my heart. I thought I was going to get an attaboy from the pattern-matching master. :( -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] OMG! Jacques gave a perfect example using pattern matching, and Jared didn't agree that it was the cat's meow to do it that way! :)
RE: sort ip addresses
Well, gee, what can I say? I hadn't yet seen your post Jacques. I was going to try and use owa_pattern myself, but didn't have time for anything but the QD method I posted. Jacques, this indeed an exquisite use of owa_pattern! All better? :) Jared Jacques Kilchoer [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 03/25/2003 02:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: sort ip addresses When I saw Jared's e-mail, it was like a dagger through my heart. I thought I was going to get an attaboy from the pattern-matching master. :( -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED] OMG! Jacques gave a perfect example using pattern matching, and Jared didn't agree that it was the cat's meow to do it that way! :) -- 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).
RE: sort ip addresses
Sort on this: lpad(substr(ip,1,instr(ip,'.')-1),3,'0') || lpad(substr(ip,instr(ip,'.')+1,instr(ip,'.',1,2)-instr(ip,'.')-1),3,'0')|| lpad(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1),3, '0')|| lpad(substr(ip,instr(ip,'.',1,3)+1),3,'0') Regards, Waleed -Original Message- Sent: Tuesday, March 25, 2003 12:54 PM 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 KE4TJC28^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: Khedr, Waleed 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).
RE: sort ip addresses
Title: RE: sort ip addresses Every Dog Has His Day! And today's mine. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Jacques, this indeed an exquisite use of owa_pattern!
