Dave
--
David M. Delbridge
Circa 3000
ColdFusion Hosting
http://www.circa3k.com
775-832-2445
Marlon Moyer wrote:
>
> Here's the start of some code that would do it. You'd need to make a
> 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]
