166.141.22.4
166.141.22.40
166.141.22.41
166.141.22.47
166.141.22.48
166.141.22.5
is wrong.
that should be
166.141.22.4
166.141.22.5
166.141.22.40
166.141.22.41
166.141.22.47
166.141.22.48
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-----Original Message-----
From: Benjamin S. Rogers [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 4:08 PM
To: CF-Talk
Subject: RE: Sorting IP Addresses
Stripped this out of a DNSBL we run (the DNSBL actually reverses the order
of octets in an IP address). This code should work, though I imagine there's
a prettier way to do it.
SELECT *
FROM SourceIPSpamCount
ORDER BY
SUBSTRING(SourceIP, 1, CHARINDEX('.', SourceIP) - 1), SUBSTRING(SourceIP,
CHARINDEX('.', SourceIP) + 1,CHARINDEX('.', SourceIP, CHARINDEX('.',
SourceIP) + 1) - CHARINDEX('.', SourceIP) - 1),
REVERSE(SUBSTRING(REVERSE(SourceIP), CHARINDEX('.', REVERSE(SourceIP))
+ 1, CHARINDEX('.', REVERSE(SourceIP), CHARINDEX('.', REVERSE(SourceIP))
+ 1) - CHARINDEX('.', REVERSE(SourceIP)) - 1)),
REVERSE(SUBSTRING(REVERSE(SourceIP), 1, CHARINDEX('.',
REVERSE(SourceIP)) - 1))
Benjamin S. Rogers
http://www.c4.net/
v.508.240.0051
f.508.240.0057
-----Original Message-----
From: David Delbridge [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 16, 2003 3:06 PM
To: CF-Talk
Subject: Sorting IP Addresses
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]
