function out of it.
declare @test varchar(50)
declare @pos1 tinyint
declare @pos2 tinyint
declare @Oct1 char(3)
declare @Oct2 char(3)
declare @Oct3 char(3)
declare @Oct4 char(3)
SET @test = '2.3.125.105'
SET @pos1 = 1
SET @pos2 = charIndex('.',@test)
SET @Oct1 = left(@test,@pos2-1)
SET @Oct1 = replicate('0',3-len(@Oct1)) + @Oct1
SET @pos1 = @pos2 + 1
SET @pos2 = charIndex('.',@test,@pos1)
SET @Oct2 = substring(@test, @pos1,@[EMAIL PROTECTED])
SET @Oct2 = replicate('0',3-len(@Oct2)) + @Oct2
SET @pos1 = @pos2 + 1
SET @pos2 = charIndex('.',@test,@pos1)
SET @Oct3 = substring(@test, @pos1,@[EMAIL PROTECTED])
SET @Oct3 = replicate('0',3-len(@Oct3)) + @Oct3
SET @pos1 = @pos2 + 1
SET @pos2 = len(@test)+1
SET @Oct4 = substring(@test, @pos1,@[EMAIL PROTECTED])
SET @Oct4 = replicate('0',3-len(@Oct4)) + @Oct4
print @oct1 + @oct2 + @Oct3 + @Oct4
marlon
[EMAIL PROTECTED]
David Delbridge wrote:
> Thanks, guys. Unfortunately, everything must happen in the query itself
> (or at the SQL server) because I'm using a custom tag which takes a raw
> SQL query as input and spits out the results in a particularly-formatted
> (and quite attractive) table. Hence, there is not a place for me to
> manipulate the data via CF.
>
> I like Raymond's link, though. I wonder if I couldn't strip the
> punctuation and convert to integer in the query, rather than parse out
> each octet?
>
> Hmmm...
>
> Dave
>
> Michael Dinowitz wrote:
> >
> > There are 2 ways I can think of. The first is to get the IPs and then
> > write
> > an IPSort() function to do the sorting for you post query. The second
> > is to
> > have SQL parse each IP part into it's own var and sort on each of the
> > vars.
> > Ugly as sin, but....
> >
> > > Hi all,
> > >
> > > I have some lengthy tables filled with IP addresses. Given that the
> > IP
> > > addresses are contained in a single text field (and not four
> > individual
> > > octet fields), what would be the SIMPLEST way to sort those IPs in a
> > SQL
> > > query?
> > >
> > > For example, 'SELECT IP FROM IP_TABLE ORDER BY IP' produces the
> > > following sort order:
> > >
> > > 123.123.123.10
> > > 123.123.123.11
> > > 123.123.123.2
> > > 123.123.123.20
> > > 123.123.123.21
> > > 123.123.123.3
> > >
> > > But, of course, the desired results would instead be:
> > >
> > > 123.123.123.2
> > > 123.123.123.3
> > > 123.123.123.10
> > > 123.123.123.11
> > > 123.123.123.20
> > > 123.123.123.21
> > >
> > > Any help is greatly appreciated.
> > >
> > > Dave
> > >
> > > --
> > >
> > > David M. Delbridge
> > > Circa 3000
> > > ColdFusion Hosting
> > > http://www.circa3k.com
> > > 775-832-2445
> > >
> >
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
