August 21, 2011 at 11:14 pm
Coming to think of it. Why don't you just "pad out" with zero's each component of the both ipv6 addresses, to "normalise" them and then do a string compare of the entire resulting addresses? That should work, as hexadecimal digits are all still in the correct order. My example routine should be easily adapted to return a padded/normalised version of an ipv6 address. And the same process can be used for IPV4. Not that we need it there, but they would be using the same method then.
August 22, 2011 at 3:26 pm
My suggestion would be to use varbinary(16). Convert the formatted strings (either in IP4 or IP6 format) into varbinary and go from there. IP6 addresses are 128 bits in hexadecimal-with-colon-separator format, so it should be fairly easy to determine which conversion to use. Once they're in varbinary, you can use the same range comparison code for either.
August 23, 2011 at 12:40 am
let me try this..thanks for reply
August 23, 2011 at 6:56 am
sqlnew (8/21/2011)
...For example: Exec IPTest 5,'179.98.120.98' should Give Positive result(say 1) as it falls in the range for LocationID 5.
...
From your example I can see that you ever want to compare one single value against one selected range.
For this task, you could get away with the following:
-- this function will format any 4 or 6 parts ip address to 000.000.000.000.000.000
create function dbo.NormilizeIpAddress (@ipaddress varchar(25))
returns varchar(25)
with schemabinding
as
begin
set @ipaddress = '.' + @ipaddress + '.' +
case when parsename(@ipaddress,1) is null then '' else '000.000.' end
while LEN(@ipaddress) < 25
begin
set @ipaddress = ISNULL(STUFF(@ipaddress,PATINDEX('%[.]_[.]%',@ipaddress),1,'.00'),@ipaddress)
set @ipaddress = ISNULL(STUFF(@ipaddress,PATINDEX('%[.]__[.]%',@ipaddress),1,'.0'),@ipaddress)
end
return substring(left(@ipaddress,len(@ipaddress)-1),2,50)
end
go
create procedure dbo.IPTest (@locId int, @ipaddress varchar(25))
as
begin
declare @ret int = 0
-- you can just compare strings as they are all formatted to the same mask.
select @ret = CASE WHEN dbo.NormilizeIpAddress(@ipaddress)
BETWEEN dbo.NormilizeIpAddress(Low) AND dbo.NormilizeIpAddress(Hig) THEN 1
ELSE 0
END
from dbo.MySampleTable
where LocId = @locId
select @ret
return @ret
end
Also it's possible to "normalize" ip address in set-based operation using recursive CTE:
declare @ipaddress varchar(25) = '12.3.23.1'
--declare @ipaddress varchar(25) = '12.3.23.1.5.6'
;with nip as
(
select cast('.' + @ipaddress + '.' +
case when parsename(@ipaddress,1) is null then '' else '000.000.' end as varchar(25)) as adr
union all
select cast(ISNULL(STUFF(ISNULL(STUFF(adr,PATINDEX('%[.]__[.]%',adr),1,'.0'),adr)
,PATINDEX('%[.]_[.]%',ISNULL(STUFF(adr,PATINDEX('%[.]__[.]%',adr),1,'.0'),adr))
,1,'.00'),ISNULL(STUFF(adr,PATINDEX('%[.]__[.]%',adr),1,'.0'),adr)) as varchar(25))
from nip
)
select top 1 adr
from (select top 6 substring(left(adr,len(adr)-1),2,50) adr from nip ) a
where len(adr) = 23
option (maxrecursion 6)
You can create table valued function with the above code and it most likely will outperform the first scalar version when "cross applying" it against recordset of values.
August 24, 2011 at 6:31 am
Comparing IPv4 addresses using text comparisons only can be done by normalizing them as follows:
declare @ipv4 table (
ip varchar(15)
);
insert @ipv4 (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 x.dig1 + '.' + x.dig2 + '.' + x.dig3 + '.' + x.dig4
from @ipv4 i
cross apply (
select '.' + i.ip + '.' as ip
) input
cross apply (
select max(case src.ix when 1 then right(replicate('0', 3) + src.value, 3) else '' end) as dig1
,max(case src.ix when 2 then right(replicate('0', 3) + src.value, 3) else '' end) as dig2
,max(case src.ix when 3 then right(replicate('0', 3) + src.value, 3) else '' end) as dig3
,max(case src.ix when 4 then right(replicate('0', 3) + src.value, 3) else '' end) as dig4
from (
select row_number() over (order by t.n) as ix,
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;
This code can easily be put into a function or better yet, in a view. It performs at its best on large sets of numbers. (actually, the estimated execution plan is funny: declaring the table and inserting the 3 ip values takes up 71% of the costs of the entire batch, i.e. the conversion itself is lots cheaper than creating the small table).
And for IPv6 address strings similar code can be used to normalize them. However it should be noted that in IPv6 addresses any :0000's can be left off. So this code sets any missing numbers to 0000 itself:
declare @ipv6 table (
ip varchar(39)
);
insert @ipv6 (ip)
select 'fe80::2168:9828:4bd8:5e2d'
union all select '2001:0DB8:AC10:FE01:0000:0000:0000:0000';
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 x.dig1 + ':' + x.dig2 + ':' + x.dig3 + ':' + x.dig4 + ':' + x.dig5 + ':' + x.dig6 + ':' + x.dig7 + ':' + x.dig8
from @ipv6 i
cross apply (
select ':' + i.ip + ':' as ip
) input
cross apply (
select right(replicate('0', 4) + isnull( max(case src.ix when 1 then src.value else '' end), ''), 4) as dig1
,right(replicate('0', 4) + isnull( max(case src.ix when 2 then src.value else '' end), ''), 4) as dig2
,right(replicate('0', 4) + isnull( max(case src.ix when 3 then src.value else '' end), ''), 4) as dig3
,right(replicate('0', 4) + isnull( max(case src.ix when 4 then src.value else '' end), ''), 4) as dig4
,right(replicate('0', 4) + isnull( max(case src.ix when 5 then src.value else '' end), ''), 4) as dig5
,right(replicate('0', 4) + isnull( max(case src.ix when 6 then src.value else '' end), ''), 4) as dig6
,right(replicate('0', 4) + isnull( max(case src.ix when 7 then src.value else '' end), ''), 4) as dig7
,right(replicate('0', 4) + isnull( max(case src.ix when 8 then src.value else '' end), ''), 4) as dig8
from (
select row_number() over (order by t.n) as ix,
upper(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 24, 2011 at 2:30 pm
Is there some reason you need to do comparisons as strings of characters? Serious question. SQL Server will quite happily compare binary values. If you convert to binary, you don't have to worry about "normalising" each section of an IP address.
August 24, 2011 at 11:28 pm
No there isn't any reason not to convert the addresses into binary(16) instead of varchar(39). It is just far more complicated to create a correct binary string using T-SQL. f.e. getting the ordering of the bytes right can be rather complicated. A string version can be easily verified to be correct. But you're wrong on not having to normalize: when creating a binary version, you'll still have to provide zero's for any missing parts, or the comparisons between 2 IP numbers will give incorrect results. 'fe80::2168:9828:4bd8:5e2d', 'fe80:0000:2168:9828:4bd8:5e2d' and 'fe80:0000:2168:9828:4bd8:5e2d:0000:0000' are all the same address.
Of course you're welcome to provide an example on how to create the binary version. Always willing to learn.
August 26, 2011 at 9:30 pm
Thank you all for the response....
August 27, 2011 at 5:14 pm
Bruce W Cassidy (8/22/2011)
My suggestion would be to use varbinary(16). Convert the formatted strings (either in IP4 or IP6 format) into varbinary and go from there. IP6 addresses are 128 bits in hexadecimal-with-colon-separator format, so it should be fairly easy to determine which conversion to use. Once they're in varbinary, you can use the same range comparison code for either.
Have you got some code we could see for such a conversion, Bruce?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2013 at 12:37 am
That function for NormalizeIpAddress should have an outer IF block!
IF LEN(@ipaddress) - LEN(REPLACE(@ipaddress, '.', '')) >= 3
BEGIN
END
...else you would be hanging your SQL server from a tree. legs up (infinity loop 🙂
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply