Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query: Order by IP

573579Apr 20 2007 — edited Nov 5 2010
Sorry for my bad english at first ;)

I have the followring Query:

SELECT * FROM GN_SERVER ORDER BY HAUPT_IP

and i need to sort this by the IP Adress, that the result show like this:

83.133.96.2
83.133.96.3
83.133.96.10
83.133.96.20
83.133.97.2
83.133.97.3
83.133.97.10
83.133.98.2
83.133.98.3
83.133.98.10
and so on

but i cant make ORDER BY HAUPT_IP this will return a false result.

How can i make the correct query?

Thanks for help.

Comments

Avinash Tripathi
Hi,
Try this:

WITH T AS (
SELECT '83.133.96.2' IP FROM DUAL UNION ALL
SELECT '83.133.96.3' FROM DUAL UNION ALL
SELECT '83.133.96.10' FROM DUAL UNION ALL
SELECT '83.133.98.10' FROM DUAL UNION ALL
SELECT '83.133.97.2' FROM DUAL UNION ALL
SELECT '83.133.97.3' FROM DUAL UNION ALL
SELECT '83.133.97.10' FROM DUAL UNION ALL
SELECT '83.133.98.2' FROM DUAL UNION ALL
SELECT '83.133.98.3' FROM DUAL UNION ALL
SELECT '83.133.96.20' FROM DUAL )


SELECT IP FROM t ORDER BY TO_NUMBER(REPLACE(IP,'.'))


Regards
Rob van Wijk 
SQL> create table gn_server
  2  as
  3  select '1.1.1.1' haupt_ip from dual union all
  4  select '10.10.10.10' from dual union all
  5  select '2.2.2.2' from dual union all
  6  select '20.20.20.100' from dual union all
  7  select '20.20.20.1' from dual union all
  8  select '100.100.100.100' from dual
  9  /

Tabel is aangemaakt.

SQL> select haupt_ip
  2    from gn_server
  3   order by haupt_ip
  4  /

HAUPT_IP
---------------
100.100.100.100
10.10.10.10
1.1.1.1
20.20.20.1
20.20.20.100
2.2.2.2

6 rijen zijn geselecteerd.

SQL> SELECT haupt_IP FROM gn_server ORDER BY TO_NUMBER(REPLACE(haupt_IP,'.'))
  2  /

HAUPT_IP
---------------
1.1.1.1
2.2.2.2
20.20.20.1
10.10.10.10
20.20.20.100
100.100.100.100

6 rijen zijn geselecteerd.

SQL> select haupt_ip
  2    from gn_server
  3   order by to_number(substr(haupt_ip,1,instr(haupt_ip,'.',1,1)-1))
  4       , to_number(substr(haupt_ip,instr(haupt_ip,'.',1,1)+1,instr(haupt_ip,'.',1,2)-instr(haupt_ip,'.',1,1)-1))
  5       , to_number(substr(haupt_ip,instr(haupt_ip,'.',1,2)+1,instr(haupt_ip,'.',1,3)-instr(haupt_ip,'.',1,2)-1))
  6       , to_number(substr(haupt_ip,instr(haupt_ip,'.',1,3)+1))
  7  /

HAUPT_IP
---------------
1.1.1.1
2.2.2.2
10.10.10.10
20.20.20.1
20.20.20.100
100.100.100.100

6 rijen zijn geselecteerd.

Regards,
Rob.

Laurent Schneider 
SELECT IP,
  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') FORMATTED_IP
 from t
order by
  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');

IP              FORMATTED_IP
--------------- ---------------
3.3.100.20      003.003.100.020
3.20.3.20       003.020.003.020
3.20.100.100    003.020.100.100
3.100.100.20    003.100.100.020
20.3.20.3       020.003.020.003
20.3.100.3      020.003.100.003
20.20.100.3     020.020.100.003
100.3.3.3       100.003.003.003
100.100.3.20    100.100.003.020
Laurent Schneider
I prefer using lpad than to_number because if some values are invalid, the to_number will generate an error, but one may prefer to receive an error
121256
Someone could prefer general and scalable solution using order member function. Example is [url http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=339732&pg=-1&hl=order+siblings#3156237]here
573579
great, it works, many thanks to all.
Aketi Jyuuzou
Same thread of OTN-Japan
http://otn.oracle.co.jp/forum/thread.jspa?threadID=35000486

and my site :-)
http://www.geocities.jp/oraclesqlpuzzle/10-152.html
col afterIP for a15
WITH T AS (
SELECT '83.133.96.2' IP FROM DUAL UNION ALL
SELECT '83.133.96.3' FROM DUAL UNION ALL
SELECT '83.133.96.10' FROM DUAL UNION ALL
SELECT '83.133.98.10' FROM DUAL UNION ALL
SELECT '83.133.97.2' FROM DUAL UNION ALL
SELECT '83.133.97.3' FROM DUAL UNION ALL
SELECT '83.133.97.10' FROM DUAL UNION ALL
SELECT '83.133.98.2' FROM DUAL UNION ALL
SELECT '83.133.98.3' FROM DUAL UNION ALL
SELECT '83.133.96.20' FROM DUAL )
select IP,
RegExp_Replace(RegExp_Replace(Ip,'(^|\.)','\1000')
              ,'(^|\.)[0-9]*([0-9]{3})'
              ,'\1\2') as afterIP
from T
order by afterIP;

IP            AFTERIP
------------  ---------------
83.133.96.2   083.133.096.002
83.133.96.3   083.133.096.003
83.133.96.10  083.133.096.010
83.133.96.20  083.133.096.020
83.133.97.2   083.133.097.002
83.133.97.3   083.133.097.003
83.133.97.10  083.133.097.010
83.133.98.2   083.133.098.002
83.133.98.3   083.133.098.003
83.133.98.10  083.133.098.010
This query is used archaic Logic of VBScript of ASP.
That provides Format Function.
right("000" & VAL,3)
cd_2
Looks a bit like 437109 ... ;-)

C.
Aketi Jyuuzou
3 years after
There is simpler new version :8}
4520585
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 17 2010
Added on Apr 20 2007
9 comments
4,744 views