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]
- Re: Sorting IP Addresses David Delbridge
- Non-Stick Session Variables? Nick Baker
- RE: Non-Stick Session Variables? Mosh Teitelbaum
- RE: Sorting IP Addresses Raymond Camden
- Re: Sorting IP Addresses Michael Dinowitz
- RE: Sorting IP Addresses Barney Boisvert
- Re: Sorting IP Addresses Jochem van Dieten
- Re: Sorting IP Addresses David Delbridge
- Re: Sorting IP Addresses Jochem van Dieten
- RE: Sorting IP Addresses Tony Weeg
- RE: Sorting IP Addresses Benjamin S. Rogers
- RE: Sorting IP Addresses Tony Weeg
- RE: Sorting IP Addresses Benjamin S. Rogers
- Re: Sorting IP Addresses Calvin Ward
- RE: Sorting IP Addresses Benjamin S. Rogers
- Re: Sorting IP Addresses Jochem van Dieten
- RE: Sorting IP Addresses Benjamin S. Rogers
- Re: Sorting IP Addresses Jochem van Dieten
- RE: Sorting IP Addresses Benjamin S. Rogers
- RE: Sorting IP Addresses Jochem van Dieten
- RE: Sorting IP Addresses Tony Weeg