August 20, 2011 at 10:55 pm
i have a table with 2 fields IpMax, IPMin
i want to compare an ip whether it is in the range(in max and Min range) or not ..How can i do that...? Ip address can be Ipv6 or Ipv4. Can some one help me out.Thanks
August 20, 2011 at 11:24 pm
Replace "." with empty and cast it as int than compare it...
CAST(REPLACE(IP4,'.','') AS INT)
August 20, 2011 at 11:33 pm
sqlnew (8/20/2011)
i have a table with 2 fields IpMax, IPMini want to compare an ip whether it is in the range(in max and Min range) or not ..How can i do that...? Ip address can be Ipv6 or Ipv4. Can some one help me out.Thanks
I'm not sure what your other table looks like but if you could provide the DDL then you the probability of getting a definitive answer would be better although someone will probable guess as to what the specification is. Sounds like a CTE Solution.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2011 at 11:37 pm
thanks for responding...i tried that but it gives wrong result...
Lets say an ex: IPMin= 206.63.71.23
IPMax= 206.63.71.255
If My IP= 206.84.71.241
If i use your statement it shows myIp is in the range of Min and Max, But actually it is not.
August 20, 2011 at 11:41 pm
So far you have only provided one table with two columns and two IP Addresses.
Thank you for the information but don't you have another table?
What does your statement that provided you with an incorrect result look like (Test Script). Nice to know information.
Do you think that you could provide the DDL and some inserts for sample data?
It's a really simple query that you need but more information is needed to solve this puzzle without making assumptions.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2011 at 11:45 pm
Forget about table and Consider some fixed values for IpMin and IPMax just like above mentioned example..if it works i can work for a table. Thanks for responding.
August 20, 2011 at 11:49 pm
sqlnew (8/20/2011)
Forget about table and Consider some fixed values for IpMin and IPMax just like above mentioned example..if it works i can work for a table. Thanks for responding.
I understand why you need a table for the MIN & MAX IP Addresses but can you share what the other table looks like and you SQL Code. If it is not too much trouble can you provide the DDL for both of your tables as well as some INSERT Statements? Please include your test data in which you are getting an incorrect result.
Please refer to the first link in my signature block.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2011 at 12:01 am
sorry for the trouble....
I have only 1 table
CREATE TABLE [dbo].[MySampleTable](
[LocId] [int] NULL,
[Low] [varchar](20) NULL,
[Hig] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[MySampleTable] ([LocId], [Low], [Hig]) VALUES (1, N'205.34.25.20', N'205.34.25.255')
INSERT [dbo].[MySampleTable] ([LocId], [Low], [Hig]) VALUES (2, N'192.87.15.100', N'192.87.20.100')
INSERT [dbo].[MySampleTable] ([LocId], [Low], [Hig]) VALUES (3, N'228.62.12.110', N'228.62.12.240')
INSERT [dbo].[MySampleTable] ([LocId], [Low], [Hig]) VALUES (4, N'162.87.12.118', N'162.87.14.254')
INSERT [dbo].[MySampleTable] ([LocId], [Low], [Hig]) VALUES (5, N'179.98.12.100', N'179.98.123.100')
I need a storeproc which takes id and ip as parameter and displays some positive result if it is the mentioned range.
For example: Exec IPTest 5,'179.98.120.98' should Give Positive result(say 1) as it falls in the range for LocationID 5.
There are no other tables involved in this assignment...i have around 700 to 800 records. Hope this info is enough...
In this example Ipv4 is mentioned but it can be Ipv6 too.
August 21, 2011 at 4:00 am
I think the nature of your problem is functional, not T-SQL related. An ip4 address is a set of 4 bytes (i.e. ranging from 0-255). Ip addresses are not defined as simple min-max ranges, these ranges are only valid when the ip addresses fall within a specific mask first. In your example 206.84.71.241 is not within the range of 206.63.71.23 - 206.63.71.255, because it is not in the same network. Next to the min-max values you would probably need something like a network mask of the form 255.255.255.0 too. Any IP is in the range if ((IP & mask) = (range & mask)) AND IP | ~MASK between min | ~MASK and max | ~MASK.
"&" = binary and, "AND" = logical and, "|" = bitwise or, "~" = bitwise not, "=" = equals, "between and " = inclusive range comparison.
Got that :hehe:?
August 21, 2011 at 4:45 am
I don't know how this will work out for IPV6 addresses, but based on the idea of taking the bytes from the ip numbers and converting these into one int value that you can compare, you would need something like below. It converts all 3 ip addresses into an integer value that can be compared.
declare @ips table (
ip varchar(15)
);
insert @ips (ip)
select '206.84.71.241'
union all select '206.63.71.23'
union all select '206.63.71.255';
with cte0 as ( select 1 as n union all select 1 union all select 1 union all select 1),
cte1 as (select 1 as n from cte0 c1, cte0 c2),
cte2 as (select 1 as n from cte1 c1, cte1 c2),
cteTally as (select row_number() over (order by (select 1)) as n from cte2)
select *
from (
select '.' + ip + '.' as ip
from @ips
) input
cross apply (
select sum(power(255, src.ix) * src.value) as intval
from (
select row_number() over (order by t.n desc) - 1 as ix,
convert(bigint, substring(input.ip, t.n + 1, -1 + charindex('.', input.ip, t.n + 1) - t.n)) as value
from cteTally t
where t.n < len(input.ip)
and substring(input.ip, t.n, 1) = '.'
) src
) x;
August 21, 2011 at 12:05 pm
R.P.Rozema (8/21/2011)
I don't know how this will work out for IPV6 addresses, but based on the idea of taking the bytes from the ip numbers and converting these into one int value that you can compare, you would need something like below. It converts all 3 ip addresses into an integer value that can be compared....
PARSENAME(@NetworkIP, n) -- where n = 1 to 4 gives a similar result, surely?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 21, 2011 at 1:40 pm
@R.P.Rozema:
It works for Ipv4 addresses..i just tried converting those 4 parts into integers and then binary.It is fine for v4...how about v6?
August 21, 2011 at 3:03 pm
Could you please post your code?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2011 at 10:46 pm
i have used this code for converting ipv4 address
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnBinaryIPv4](@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
DECLARE @bin AS BINARY(4)
SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
RETURN @bin
END
then i compared using dis function...
August 21, 2011 at 10:59 pm
The comparison method doesn't work for IPV6. For IPV4 I already had to use bigints to hold a unique integer for each address, for IPV6 the number of addresses is simply too big to hold in any single integer type that T-SQL supports: BOL on int ranges and wiki on ip address ranges.
i.e. You'll need a more advanced method to compare IPV6 addresses. something along the lines of comparing the most significant value, and ony if these are the same, compare the lesser significant value, and only if this is the same compare the even lesser significant value, etc. You can still use code derived from my example to 'read' the numerical values of the ip address components. And in response to Chris' suggestion: parsename could indeed be (mis-?)used for IPV4 addresses, but it won't be useful for IPV6 addresses, as these contain 6 sections, plus they use ":" as a separator.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply