Oracle: Datensätze nach IP sortieren

[Gray]

Ich möchte mein Ergebnis nach IPs sortieren, INET_ATON funktioniert jedoch bei Oracle nicht, gibts da eine alternative?

 

[m3000]

Bitte Datenbeispiel angeben und Deinen select-Versuch. Ausserdem das Problem genauer beschreiben. Sortierung erreichst Du mit order by, aber das wird wohl kaum gemeint sein?!

 

[Gray]

Hm, also sorry aber das das so schwer zu verstehen ist!?

Wie IPs aussehen weis ja jeder
192.168.0.1
192.168.0.2
192.168.0.10
192.168.0.20
usw.

von selbst werden diese (wenn ich nur order by IP versuch), wie folgt sortiert:

192.168.0.10
192.168.0.1
192.168.0.20
192.168.0.2
usw.

ich möchte es aber so:
192.168.0.1
192.168.0.2
192.168.0.10
192.168.0.20

ich hoffe das war verständlich genug.

 

[MPr]

man kann zwar über den Parameter nls_sort einige Varianten der Sortierung erreichen, aber die gewünschte Sortierung der IP scheint mir damit nicht möglich zu sein (weil die IP als String behandelt wird). Möglicherweise könnte man sich damit behelfen, den IP-String zu zerlegen und die Sortierung dann über die in Zahlen umgewandelten Substrings erfolgen zu lassen, also z.B.:

SQL> select * from test order by ip;

IP
--------------------
192.168.0.1
192.168.0.10
192.168.0.2
192.168.0.20

SQL> select t.*, substr(ip, 11) from test t order by to_number(substr(ip, 11));

IP                   SUBSTR(IP,
-------------------- ----------
192.168.0.1          1
192.168.0.2          2
192.168.0.10         10
192.168.0.20         20

Gruß

MP

 

[Gray]

klappt leider nicht ganz, da ja die IPs 1-3 stellen pro Block haben können und es so jeden Block prüfen muss, man bräuchte einen Exploder der anhand des Punktes die IP trennt und nach diesen Zahlen dan sortieren.

 

[Radhad]

Kann man Substrings anhand eines Seperators erstellen? Dann könntest du es anhand des Punktes trennen.

 

[MPr]

sollte sich über die INSTR Funktion lösen lassen, so lange die IPs immer drei Punkte enthalten, also z.B.:

SQL> select *
  2    from (select t.*,
  3                 to_number(substr(ip, instr(ip, '.', 0, 1), instr(ip, '.', 1, 1) - 1)) sub1,
  4                 to_number(substr(ip, instr(ip, '.', 1, 1) + 1, (instr(ip, '.', 1, 2) - 1 - instr(ip, '.', 1, 1)))) sub2,
  5                 to_number(substr(ip, instr(ip, '.', 1, 2) + 1, (instr(ip, '.', 1, 3) - 1 - instr(ip, '.', 1, 2)))) sub3,
  6                 to_number(substr(ip, instr(ip, '.', 1, 3) + 1)) sub4
  7            from test t
  8          )
  9   order by sub1, sub2, sub3, sub4;

IP                         SUB1       SUB2       SUB3       SUB4
-------------------- ---------- ---------- ---------- ----------
127.0.0.1                   127          0          0          1
192.168.0.1                 192        168          0          1
192.168.0.2                 192        168          0          2
192.168.0.10                192        168          0         10
192.168.0.20                192        168          0         20
192.168.15.15               192        168         15         15

6 Zeilen ausgewählt.

Gruß

MP

 

[Exceptionfault]

Wenn du Oracle 10g benutzt, kannst du auch die Regular Expression Funktionen nutzen. In diesem Fall würde man die IP Adressen ersteinmal in ein einheitliches Format (xxx.xxx.xxx.xxx) bringen, und dann einfach sortieren.

SELECT ip, 
        REGEXP_REPLACE(
            REGEXP_REPLACE( ip, '([0-9]+)(\.?)', '00\1\2'),                  
            '[0-9]*([0-9]{3})(\.?)', '\1\2'
        ) AS STD_IP
  FROM ip
  order by std_ip
; 

IP              STD_IP
--------------- ---------------
127.0.0.1       127.000.000.001
192.168.0.1     192.168.000.001
192.168.0.2     192.168.000.002
192.168.0.10    192.168.000.010
192.168.0.20    192.168.000.020
192.168.15.15   192.168.015.015

Aus Performance Gründen würde ich sogar empfehlen, die Formatierung auf das "3-er Tupel" Format bereits beim INSERT durchzuführen. Evtl. mit einem Trigger der auch gleich auf ein gültiges Format prüft. Nur durch die einheitliche Formatierung lässt sich auch wirklich sicher ein UNIQUE Constraint umsetzen.

 

[Gray]

thx,

gesamt sieht das bei mir jetzt so aus:

$result = ociparse($db, "SELECT HAUPT_IP, REGEXP_REPLACE(REGEXP_REPLACE(HAUPT_IP, '([0-9]+)(\.?)', '00\1\2'),'[0-9]*([0-9]{3})(\.?)', '\1\2') AS STD_IP  FROM GN_SERVER ORDER BY STD_IP");

allerdings bekomme ich wenn ich mir STD_IP ausgeben lasse, bei jeder Zeile als Ergebnis das hier: 00
00
00
00
 wodurch es natürlich nicht sortieren kann.

Ich hab leider nicht viel ahnung bezüglich RegExp und find auch nicht wirklich sinnvolle erklärungen die mir helfen würden.

 

[lmarkus31]

Hallo zusammen,

für den Fall, dass jemand nach einer anderen Lösung sucht:

Im Prinzip hatte MPr die Lösung ja schon genannt, nur kann man sich die Subquery sparen: Ich habe mal seine INSTR-Lösung genommen:

    select * from test 
order by to_number(substr(ip, instr(ip, '.', 0, 1), instr(ip, '.', 1, 1) - 1)),
         to_number(substr(ip, instr(ip, '.', 1, 1) + 1, (instr(ip, '.', 1, 2) - 1 - instr(ip, '.', 1, 1)))),
         to_number(substr(ip, instr(ip, '.', 1, 2) + 1, (instr(ip, '.', 1, 3) - 1 - instr(ip, '.', 1, 2)))),
         to_number(substr(ip, instr(ip, '.', 1, 3) + 1));

Ich schliesse mich allerdings der Meinung von Exceptionfault an, dass man evtl. die Daten schon beim Inserten / Updaten per Trigger in eine geeignetere Form bringt oder von vorherein eine andere Struktur zur Ablage wählt.

Markus