Re: Sorting IP Addresses
That sounds like it could perform poorly, would it?
- Calvin
- Original Message -
From: Benjamin S. Rogers
To: CF-Talk
Sent: Thursday, October 16, 2003 4:38 PM
Subject: RE: Sorting IP Addresses
that doesn't work perfectly.
166.141.22.4
166.141.22.40
166.141.22.41
166.141.22.47
166.141.22.48
166.141.22.5
Sorry about that. I forgot to convert the substrings to integers. Try
this:
SELECT *
FROM SourceIPSpamCount
ORDER BY
CONVERT(INT, SUBSTRING(SourceIP, 1, CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, SUBSTRING(SourceIP, CHARINDEX('.', SourceIP) +
1,CHARINDEX('.', SourceIP, CHARINDEX('.', SourceIP) + 1) -
CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), CHARINDEX('.',
REVERSE(SourceIP)) + 1, CHARINDEX('.', REVERSE(SourceIP), CHARINDEX('.',
REVERSE(SourceIP)) + 1) - CHARINDEX('.', REVERSE(SourceIP)) - 1))),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), 1, CHARINDEX('.',
REVERSE(SourceIP)) - 1)))
Benjamin S. Rogers
http://www.c4.net/
v.508.240.0051
f.508.240.0057
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Sorting IP Addresses
Well, performance is relative. However, it is a solution which overcomes
a lack of native support for an IP data type and addresses Dave's
requirement that everything must happen in the query itself.
As I said, this bit of code was excerpted and adapted from a DNSBL we
run. We found that this chunk of code, when executing it against 150,000
records, was too slow to be used in real time DNS requests. The DNS
requests would take about 5 seconds, which meant that our SMTP banner
would take just as long to appear. There is actually a good deal more
going on, but that's the gist of it.
Our solution was to convert the query to a view. A scheduled task runs
every 15 minutes and populates a table from the view. Now, DNS requests
take milliseconds and the SMTP banner appears almost immediately.
So, for us, performance was an issue, but we did not have similar
requirements to Dave.
Benjamin S. Rogers
http://www.c4.net/
v.508.240.0051
f.508.240.0057
-Original Message-
From: Calvin Ward [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 4:35 AM
To: CF-Talk
Subject: Re: Sorting IP Addresses
That sounds like it could perform poorly, would it?
- Calvin
- Original Message -
From: Benjamin S. Rogers
To: CF-Talk
Sent: Thursday, October 16, 2003 4:38 PM
Subject: RE: Sorting IP Addresses
that doesn't work perfectly.
166.141.22.4
166.141.22.40
166.141.22.41
166.141.22.47
166.141.22.48
166.141.22.5
Sorry about that. I forgot to convert the substrings to integers. Try
this:
SELECT *
FROM SourceIPSpamCount
ORDER BY
CONVERT(INT, SUBSTRING(SourceIP, 1, CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, SUBSTRING(SourceIP, CHARINDEX('.', SourceIP) +
1,CHARINDEX('.', SourceIP, CHARINDEX('.', SourceIP) + 1) -
CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), CHARINDEX('.',
REVERSE(SourceIP)) + 1, CHARINDEX('.', REVERSE(SourceIP),
CHARINDEX('.',
REVERSE(SourceIP)) + 1) - CHARINDEX('.', REVERSE(SourceIP)) - 1))),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), 1, CHARINDEX('.',
REVERSE(SourceIP)) - 1)))
Benjamin S. Rogers
http://www.c4.net/
v.508.240.0051
f.508.240.0057
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Sorting IP Addresses
Sorry about that. I was busy yesterday, but I saw a question that I thought I could provide one possible answer too so I rattle it off a couple of e-mails without looking back at the thread. And I didn't take offense to Jochem's quip: my apologies to Jochem if it seemed that way. Benjamin S. Rogers http://www.c4.net/ v.508.240.0051 f.508.240.0057 -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 4:45 PM To: CF-Talk Subject: RE: Sorting IP Addresses not for me :) im not the one lookin, it was , david delbridge. and don't mind jochem, I think that's just his brand of humor. although not very funny sometimes, its just jochem, and we deal. he is a db wizard. ...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:33 PM To: CF-Talk Subject: RE: Sorting IP Addresses Sure. I would use: SELECT* FROM SourceIPSpamCount ORDER BYSourceIP But I don't use MS SQL Server ;-) I'm afraid that comment was lost on me? If you could give me a bit more information, perhaps I could come up with an equivalent in SQL Server for Tony. Benjamin S. Rogers http://www.c4.net/ v.508.240.0051 f.508.240.0057 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
Somebody wrote IP, network and MAC data types for PostgreSQL, And how far along are they in porting that to Windows? :) including support for the big seven operators. So I could just use the IP datatype for SourceIP and everything would work automagically: That's pretty handy. Out of curiosity, does it also include functions for selecting individual octets? If not, how about user defined functions? That would make it useful for what we're doing. Since it is my understanding that you can't write your own operators in MS SQL Server, I doubt this is going to fly. I don't know of a SQL Server equivalent functionality wise. Perhaps Yukon with C# will address this, though a multi-thousand dollar upgrade for an IP datatype would just be silly. Benjamin S. Rogers http://www.c4.net/ v.508.240.0051 f.508.240.0057 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
Benjamin S. Rogers said: Somebody wrote IP, network and MAC data types for PostgreSQL, And how far along are they in porting that to Windows? :) SRA and some others sell commercial versions, 7.2.1 is available as a free source distribution. including support for the big seven operators. So I could just use the IP datatype for SourceIP and everything would work automagically: That's pretty handy. Out of curiosity, does it also include functions for selecting individual octets? If not, how about user defined functions? That would make it useful for what we're doing. There isn't really code to select individual octets, but you could write something for it in any of the available procedural languages. But do you really want to select individual octets or is that just the workaround you use now? Because there are operators to check for subnet inclusion and functions to calculate broadcast address, network address etc from subnet definitions. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sorting IP Addresses
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]
RE: Sorting IP Addresses
Would this help? http://www.cflib.org/udf.cfm?ID=946 I'm not sure if the dec rep of the IP will be in the same order though. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
Probably easier to split into octets first, and then use those.You'll have to split them anyway to do the sorting, so better to do it once, rather than as part of each query. -Original Message- From: David Delbridge [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 12: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
It should be. Would this help? http://www.cflib.org/udf.cfm?ID=946 I'm not sure if the dec rep of the IP will be in the same order though. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sorting IP Addresses
David Delbridge wrote: 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? What database? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sorting IP Addresses
SQL 2000 Server. Thanks. Jochem van Dieten wrote: David Delbridge wrote: 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? What database? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sorting IP Addresses
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]
RE: Sorting IP Addresses
Then you'd have to figure out how to put the periods back in... in the right places.:-) -Original Message- From: David Delbridge [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 3:34 PM To: CF-Talk Subject: Re: Sorting IP Addresses 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 http://www.circa3k.com 775-832-2445 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
Well, if he _could_ do work after the query and before the display, he could easily make a structure where the key is the IP and the value is dec version. Then he could just use structSort to get a list of sorted keys. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sorting IP Addresses
David Delbridge wrote: SQL 2000 Server. That's too bad. Probably the easiest is to use a function that splits the octets. Slightly harder a function left-pads the different octets. Another option would be to convert to an integer (remember that you need all 32 bits, so a signed INT4 will not do). Whether to convert on the fly, store in padded format or store in both unpadded and padded format depends on the usage pattern. (Can MS SQL Server do functional indexes?) How do you intend to handle IPv6 addresses? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
I stand corrected :-) -Original Message- From: Raymond Camden [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 3:44 PM To: CF-Talk Subject: RE: Sorting IP Addresses Well, if he _could_ do work after the query and before the display, he could easily make a structure where the key is the IP and the value is dec version. Then he could just use structSort to get a list of sorted keys. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
Dave, 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... What I'd recommend doing is storing the IP address in integer format in the database (you can store the IP in octet format as well.) That way you just need to sort by the int value. Here's some code to convert an IP address to its integer value that I just whipped up: cfscript sIp = 192.168.1.23; iIpInt = (listFirst(sIp, .) * 16777216) + (listGetAt(sIp, 2, .) * 65536) + (listGetAt(sIp, 3, .) * 256) + listLast(sIp, .); /cfscript Feel free to wrap it up in a UDF. Now when storing an IP address, just store it's int value as well. -Dan [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
I have a little thingie built (soon to be cfc) that takes an ip address, strips the .'s out makes it one big number 166.141.23.1 166.141.23.10 166.141.23.2 166.141.23.20 and then you could order them because they then become 166141023001 166141023002 166141023010 166141023020 so that we can store it in sql server, as a bigInt, maybe you could use it inline during the looping through the query results to establish an order? then go from there? make sense? ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Tangorre, Michael [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 3:37 PM To: CF-Talk Subject: RE: Sorting IP Addresses Then you'd have to figure out how to put the periods back in... in the right places.:-) -Original Message- From: David Delbridge [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 3:34 PM To: CF-Talk Subject: Re: Sorting IP Addresses 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 http://www.circa3k.com 775-832-2445 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
that's what I build, the left pad of the octets :) ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2003 3:46 PM To: CF-Talk Subject: Re: Sorting IP Addresses David Delbridge wrote: SQL 2000 Server. That's too bad. Probably the easiest is to use a function that splits the octets. Slightly harder a function left-pads the different octets. Another option would be to convert to an integer (remember that you need all 32 bits, so a signed INT4 will not do). Whether to convert on the fly, store in padded format or store in both unpadded and padded format depends on the usage pattern. (Can MS SQL Server do functional indexes?) How do you intend to handle IPv6 addresses? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
Why not fix the IPs in the database to be zero padded for each octet on the way IN? 117.041.145.002 Then sorting is easy. Just a thought Jerry Johnson [EMAIL PROTECTED] 10/16/03 03:44PM Well, if he _could_ do work after the query and before the display, he could easily make a structure where the key is the IP and the value is dec version. Then he could just use structSort to get a list of sorted keys. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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]
RE: Sorting IP Addresses
that doesn't work perfectly.
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]
Re: Sorting IP Addresses
Benjamin S. Rogers wrote: 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. Sure. I would use: SELECT* FROM SourceIPSpamCount ORDER BYSourceIP But I don't use MS SQL Server ;-) Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
Sure. I would use: SELECT* FROM SourceIPSpamCount ORDER BYSourceIP But I don't use MS SQL Server ;-) I'm afraid that comment was lost on me? If you could give me a bit more information, perhaps I could come up with an equivalent in SQL Server for Tony. Benjamin S. Rogers http://www.c4.net/ v.508.240.0051 f.508.240.0057 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Sorting IP Addresses
that doesn't work perfectly.
166.141.22.4
166.141.22.40
166.141.22.41
166.141.22.47
166.141.22.48
166.141.22.5
Sorry about that. I forgot to convert the substrings to integers. Try
this:
SELECT *
FROM SourceIPSpamCount
ORDER BY
CONVERT(INT, SUBSTRING(SourceIP, 1, CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, SUBSTRING(SourceIP, CHARINDEX('.', SourceIP) +
1,CHARINDEX('.', SourceIP, CHARINDEX('.', SourceIP) + 1) -
CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), CHARINDEX('.',
REVERSE(SourceIP)) + 1, CHARINDEX('.', REVERSE(SourceIP), CHARINDEX('.',
REVERSE(SourceIP)) + 1) - CHARINDEX('.', REVERSE(SourceIP)) - 1))),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), 1, CHARINDEX('.',
REVERSE(SourceIP)) - 1)))
Benjamin S. Rogers
http://www.c4.net/
v.508.240.0051
f.508.240.0057
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Sorting IP Addresses
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]
Re: Sorting IP Addresses
Benjamin S. Rogers wrote:
Sure. I would use:
SELECT*
FROM SourceIPSpamCount
ORDER BYSourceIP
But I don't use MS SQL Server ;-)
I'm afraid that comment was lost on me? If you could give me a bit more
information, perhaps I could come up with an equivalent in SQL Server
for Tony.
Somebody wrote IP, network and MAC datatypes for PostgreSQL,
including support for the big seven operators. So I could just
use the IP datatype for SourceIP and everything would work
automagically:
jochemd= create table iptest (ip inet);
jochemd= insert into iptest (ip) values ('10.0.17.3');
jochemd= insert into iptest (ip) values ('10.0.1.3');
jochemd= insert into iptest (ip) values ('10.1.1.3');
jochemd= insert into iptest (ip) values ('1.1.1.3');
jochemd= insert into iptest (ip) values ('9.9.9.9');
jochemd= select * from iptest order by ip ;
ip
---
1.1.1.3
9.9.9.9
10.0.1.3
10.0.17.3
10.1.1.3
(5 rows)
jochemd=
Since it is my understanding that you can't write your own
operators in MS SQL Server, I doubt this is going to fly.
Jochem
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Sorting IP Addresses
not for me :) im not the one lookin, it was , david delbridge. and don't mind jochem, I think that's just his brand of humor. although not very funny sometimes, its just jochem, and we deal. he is a db wizard. ...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:33 PM To: CF-Talk Subject: RE: Sorting IP Addresses Sure. I would use: SELECT* FROM SourceIPSpamCount ORDER BYSourceIP But I don't use MS SQL Server ;-) I'm afraid that comment was lost on me? If you could give me a bit more information, perhaps I could come up with an equivalent in SQL Server for Tony. Benjamin S. Rogers http://www.c4.net/ v.508.240.0051 f.508.240.0057 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Sorting IP Addresses
Thanks, everyone!GREAT recommendations!
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]
