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.
> 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
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
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?
- Cal
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
&
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 @
avtrak.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
Benjamin S. Rogers wrote:
>>Sure. I would use:
>>
>>SELECT *
>>FROM SourceIPSpamCount
>>ORDER BY SourceIP
>>
>>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
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 @pos
> 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('.',
> Sure. I would use:
>
> SELECT *
> FROM SourceIPSpamCount
> ORDER BY SourceIP
>
> 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. Ro
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 BY SourceIP
But I
, 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
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, CHAR
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
-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
ginal 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: Dav
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 a
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
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
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]
[Us
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. Unfortunat
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 t
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 t
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 Thread
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]
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
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]
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 w
29 matches
Mail list logo
