Re: Sorting varchars that contain IP addresses

Here's something you might try. It only assumes there are 4 octets but doesn't assume they are equal in length.

CREATE TABLE aa_test(IP VARCHAR2(15));

INSERT INTO aa_test VALUES('123.456.789.12');
INSERT INTO aa_test VALUES('123.56.789.2');
INSERT INTO aa_test VALUES('129.83.149.10');
INSERT INTO aa_test VALUES('129.83.149.2');
INSERT INTO aa_test VALUES('129.83.149.11');
INSERT INTO aa_test VALUES('129.83.149.21');

SELECT ip
FROM aa_test
ORDER BY

TO_NUMBER(SUBSTR(ip,1,INSTR(ip,'.',1,1))),
TO_NUMBER(SUBSTR(ip,INSTR(ip,'.',1,1)+1,INSTR(ip,'.',1,2)-INSTR(ip,'.',1,1))),
TO_NUMBER(SUBSTR(ip,INSTR(ip,'.',1,2)+1,INSTR(ip,'.',1,3)-INSTR(ip,'.',1,2))),
TO_NUMBER(SUBSTR(ip,INSTR(ip,'.',1,3)+1));

DROP TABLE aa_test;

Joe Whalley wrote:
>
> I am trying to retrieve a list of IP numbers that are stored as
> character strings. When I sort them, I want them in "numerical" order,
> but since they are strings, they get sorted as text, e.g.:
>
> 129.83.149.10
> 129.83.149.11
> 129.83.149.2
> 129.83.149.21
>
> What I want is:
> 129.83.149.2
> 129.83.149.10
> 129.83.149.11
> 129.83.149.21
>
> Any good ideas?
>
> Thanks - Joe
  Received on Fri Jul 25 1997 - 00:00:00 CDT