Re: Sorting varchars that contain IP addresses

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/07/22
Message-ID: <33d60813.21586850@newshost>#1/1

On Tue, 22 Jul 1997 14:33:44 -0400, Joe Whalley <jw_at_mitre.org> 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

don't know if you think this is a good idea or not but it works:

select host
from hosts where rownum < 10
order by
to_number(substr( host, 1, instr( host, '.', 1, 1 )-1 )),

to_number(substr(host,instr(host,'.',1,1)+1,

                      instr(host,'.',1,2)-instr( host, '.', 1, 1 )-1) ),

to_number( substr( host, instr( host,'.', 1,2 )+1, 
                         instr( host, '.', 1, 3 )-instr( host, '.', 1, 2 )-1) ),

to_number( substr( host, instr( host, '.', 1, 3 )+1 )) /

HOST

---

138.1.4.119
138.1.4.149
138.1.4.160
138.1.4.221
138.2.10.177
138.2.100.27
138.2.100.113
138.2.102.30
138.2.102.101
 

It will work 'good' for moderately sized result sets as no indexes can be used to order the data (eg: use this for a couple hundred rows, not for a couple hundred thousand)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 22 1997 - 00:00:00 CDT