May 18, 2007 at 12:10 am
Hi All,
I hv a table named GeoIP and this table has 5 col.
(ID, IPFrom, IPTo, CountryName, CountryCode)
All field except ID has a type of varchar and data like : -
1 2.6.190.56 2.6.190.63 United Kingdom GB
2 3.0.0.0 4.17.135.31 United States US
3 4.17.135.32 4.17.135.63 Canada CA
4 4.17.135.64 4.18.32.71 United States US
5 4.18.32.72 4.18.32.79 Mexico MX
6 4.18.32.80 4.23.82.127 United States US
if i want to find IP 3.112.22.231 then wht I do.?
This IP address in between second record....
/*CREATE Proc Get_Country_By_IP (@Ip varchar(20))
AS Begin
-- set @Ip = '12.10.208.0'
Declare @Ip1 Int, @Ip2 Int, @Ip3 Int, @Ip4 Int, @k int
select @k = 1
select @k = CHARINDEX( '.', @Ip)
select @Ip1 = substring (@Ip,1,@k-1)
select @Ip1=ltrim(@Ip1)
select @Ip1=rtrim(@Ip1)
select @Ip=substring (@Ip,@k+1,Len(@Ip))
select @k = CHARINDEX( '.', @Ip)
select @Ip2 = substring (@Ip,1,@k-1)
select @Ip2=ltrim(@Ip2)
select @Ip2=rtrim(@Ip2)
select @Ip=substring (@Ip,@k+1,Len(@Ip))
select @k = CHARINDEX( '.', @Ip)
select @Ip3 = substring (@Ip,1,@k-1)
select @Ip3=ltrim(@Ip3)
select @Ip3=rtrim(@Ip3)
select @Ip=substring (@Ip,@k+1,Len(@Ip))
set @Ip4 = @Ip
/*select @k = CHARINDEX( '.', @Ip)
select @Ip4 = substring (@Ip,1,@k-1)
select @Ip4=ltrim(@Ip4)
select @Ip4=rtrim(@Ip4)
select @Ip=substring (@Ip,@k+1,Len(@Ip)) */
print @Ip1
print @Ip2
print @Ip3
print @Ip4
END
GO*/
Wht should i do now..?
May 18, 2007 at 12:27 am
How about changing the schema of your table? can you do this? This query is difficult because you are storing IPs as strings. It would be a much simpler query if you stored each one as 4 tinyints.
---------------------------------------
elsasoft.org
May 18, 2007 at 1:08 am
More homework?
(http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=366682)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2007 at 1:16 am
It is impossible to change the schema of table. bcoz this table has 66000 records.
There is no other solution..?
May 18, 2007 at 1:22 am
60000 rows is small. Shouldn't have trouble changing the schema for that.
Otherwise, substrings and comparisons.
The commented out stored proc has the right idea with the substrings. You'll just have to compare the results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2007 at 1:48 am
But how can i compare the result..?
May 18, 2007 at 1:52 am
Between works quite well.
Think about what it means for an ip address to be in a range.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2007 at 7:07 am
Between works quite well |
Especially if bigints
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2007 at 7:29 am
There's a function in SQL specifially designed to handle IP addresses and similar naming... your instructor probably told you about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2007 at 10:07 am
IP addresses are nothing more than a 32 bit integer converted into a more human readable form. This is easy once you master converting IP addresses to integers and that's easy as well. There are four 8 byte integers that makeup an IP address hence the 192.168.1.1 notation. Here are two functions I keep in my 'toolbox' which will convert IP address to integers and vice-versa. As you might be able to tell, these functions work with IPv4 addresses only so if you want functions that'll work with IPv6 addresses then you should be able to update these to do just that.
Just convert the IP addresses to numbers then do the comparison.
CREATE FUNCTION dbo.AddressToIPv4
(
@Address BIGINT
)
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @ClassA AS BIGINT,
@ClassB AS BIGINT,
@ClassC AS BIGINT,
@ClassD AS BIGINT
SET @ClassD = @Address & 255
SET @ClassC = (@Address & 65535) / 256
SET @ClassB = (@Address & 16777215) / 65536
SET @ClassA = @Address / 16777215
RETURN CAST(@ClassA AS VARCHAR(30)) + '.' +
CAST(@ClassB AS VARCHAR(30)) + '.' +
CAST(@ClassC AS VARCHAR(30)) + '.' +
CAST(@ClassD AS VARCHAR(30))
END
GO
CREATE FUNCTION dbo.IPv4ToAddress
(
@IP VARCHAR(15)
)
RETURNS BIGINT
AS
BEGIN
RETURN 16777216 * CAST(PARSENAME(@IP, 4) AS BIGINT) +
65536 * CAST(PARSENAME(@IP, 3) AS BIGINT) +
256 * CAST(PARSENAME(@IP, 2) AS BIGINT) +
CAST(PARSENAME(@IP, 1) AS BIGINT)
END
GO
May 23, 2007 at 4:25 am
Plz give an example how to use these function from Stored procedure...
May 24, 2007 at 12:37 am
The same way you'd use it outside a stored proc, or anywhere else for that matter
SET <variable> = dbo.<functionname>(<parameter list> )
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2007 at 12:48 am
Thanx Gail....
May 24, 2007 at 11:25 am
As has already been pointed out, your main problem is your having the IPs stored as strings, so comparison are lexical, i.e. '10' comes before '2'. However, if you really can't change the table def, here is a workaround. First, write a one-shot procedure that will go through and massage your data, making sure all the subfields in the IPs are zero-padded to three digits. Then the query becomes simple.
Here's an example using the data you gave. Notice the slightly different format of the IPs.
insert into dbo.GeoIP (IPFrom, IPTo, CountryName, CountryCode) select '002.006.190.056', '002.006.190.063', 'United Kingdom', 'GB' union all select '003.000.000.000', '004.017.135.031', 'United States', 'US' union all select '004.017.135.032', '004.017.135.063', 'Canada', 'CA' union all select '004.017.135.064', '004.018.032.071', 'United States', 'US' union all select '004.018.032.072', '004.018.032.079', 'Mexico', 'MX' union all select '004.018.032.080', '0040.23.082.127', 'United States', 'US'; select CountryName from dbo.GeoIP where '003.112.022.231' between IPfrom and IPto; CountryName =========== United States
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
May 24, 2007 at 10:52 pm
Thanx Tomm, It's good logic.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply