Searching Query problem..
-
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..?
-
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 -
More homework?
(http://qa.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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
It is impossible to change the schema of table. bcoz this table has 66000 records.
There is no other solution..?
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
But how can i compare the result..?
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Between works quite well Especially if bigints
Far away is close at hand in the images of elsewhere.
Anon. -
There's a function in SQL specifially designed to handle IP addresses and similar naming... your instructor probably told you about it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
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
-
Plz give an example how to use these function from Stored procedure...
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanx Gail....
-
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 StatesTomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
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