Re: Sorting varchars that contain IP addresses

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

I'd create another table with the parts of the IP addr broken out, and then join to that table.

create table ipaddr

( ipaddr_char  varchar2(15)  primary key
, ipaddr_p1 number(3) not null
, ipaddr_p2 number(3) not null
, ipaddr_p3 number(3) not null
, ipaddr_p4 number(3) not null

order by
 ipaddr_p1, ipaddr_p2, ipaddr_p3, ipaddr_p4

I'd add a UNIQUE constraint on the parts also. If you want to get fancy, add CHECK constraints on the parts to require the numbers to be between 0 and 255.

Later, you'll have to expand the table to handle IPv6 !

--
To reply: use swd at strata-group dot com