Re: Sorting varchars that contain IP addresses

Steve Dover wrote:
>
> 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
 

Hi,
TRY THIS SQL STATEMENT (assuming table name as 'table'

                         and column as 'IP')

select * from table
order by TO_NUMBER(substr(IP,1,3)||SUBSTR(IP,5,2)|| SUBSTR(IP,8,3)||SUBSTR(IP,12,2))

-- 
  AMARENDRA B NETTEM ( http://www.iit.edu/~nettama)
  Certified Oracle DBA
  WHITTMAN-HART Inc.
  CHICAGO.