Sqlserver IPV6 handling function or numeric conversion

Question

I'm looking for ways to handle IPv6 CIDR address ranges in SqlServer.

The data I obtained is formatted as bellow and has these columns :

Int_IP_Start, Int_IP_End, CIDR_Range, ASN, Name

for IPv4

"2868826112","2868826623","170.254.208.0/23","265116","Wave Net"
"2868827136","2868828159","170.254.212.0/22","265381","Furtado & Furtado Provedores LTDA"
"2868828928","2868829183","170.254.219.0/24","264770","Leonir Remussi (KDMNET)"
"2868829184","2868829439","170.254.220.0/24","265373","NET TELECOMUNICACOES LTDA - ME"
"2868829440","2868829695","170.254.221.0/24","265373","NET TELECOMUNICACOES LTDA - ME"
"2868830208","2868831231","170.254.224.0/22","265382","TELECOM LTDA ME"
"2868831232","2868831487","170.254.228.0/24","27951","Media Commerce Partners S.A"

And this is the same but for IPv6

"58568835385568506466387976054061924352","58568835464796668980652313647605874687","2c0f:f288::/32","328039","JSDAAV-ZA-Telecoms-AS"
"58568842991472107835764385034281156608","58568842991473316761583999663455862783","2c0f:f2e8::/48","37182","TERNET"
"58568844892948008178108487279335964672","58568844892949217103928101908510670847","2c0f:f300::/48","37153","Hetzner"
"58568847428249208634567290272742375424","58568847507477371148831627866286325759","2c0f:f320::/32","37126","BELL-TZ"
"58568849329725108976911392517797183488","58568849408953271491175730111341133823","2c0f:f338::/32","327983","Interworks-Wireless-Solutions"

The numerical reprensentation of a single IPv6 is quite large as the address space is the equivalent of a 128bit integer.

The goal here is to have a way for a database query to return wether or not an IP is part of one of the stored ranges that came from the CSV.

For IPv4 that's easy, you take the IP and convert it to it's INT32 equivalent.

Since there is no INT128 datatype in SqlServer I've got the following questions :

-Is there a way to create a custom type that would store a bigint of 128bits ? (Only need to perform Greater than or lesser than operations)

-Is there a way to properly handle IP address ranges in SqlServer ?

EDIT:

Here is the largest number an IPv6 address can represent :

    string LongestIp = "ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff";
    var SerializedIp = IPNetwork.ToBigInteger(IPAddress.Parse(LongestIp));
    Console.WriteLine(SerializedIp.ToString());

This c# code outputs 340282366920938463463374607431768211455

Trying to insert this

declare @ipv6Decimals table (Ip decimal(38,0));

insert into @ipv6Decimals (Ip) values (58568844892949217103928101908510670847); --this is okay,
--When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1
insert into @ipv6Decimals (Ip) values (99999999999999999999999999999999999999); --This is the largest numeric(38,0) will fit
insert into @ipv6Decimals (Ip) values (340282366920938463463374607431768211455);-- This is too large

Msg 1007, Level 15, State 1, Line 3 The number '340282366920938463463374607431768211455' is out of the range for numeric representation (maximum precision 38).

Answer 1

Is there a way to create a custom type that would store a bigint of 128bits ? (Only need to perform Greater than or lesser than operations)

You might not need a custom type - numeric(38,0) might be suitable here (though I don't know the full range for valid 128-bit integers or whether IPv6 might be constrained to the range of values that would fit):

CREATE TABLE #ips
(
  iplow numeric(38,0),
  iphigh numeric(38,0),
  c varchar(64),
  d int,
  e nvarchar(128)
);

INSERT #ips VALUES
(58568835385568506466387976054061924352,58568835464796668980652313647605874687,
    '2c0f:f288::/32',328039,N'JSDAAV-ZA-Telecoms-AS'),
(58568842991472107835764385034281156608,58568842991473316761583999663455862783,
    '2c0f:f2e8::/48',37182 ,N'TERNET'),
(58568844892948008178108487279335964672,58568844892949217103928101908510670847,
    '2c0f:f300::/48',37153 ,N'Hetzner'),
(58568847428249208634567290272742375424,58568847507477371148831627866286325759,
    '2c0f:f320::/32',37126 ,N'BELL-TZ'),
(58568849329725108976911392517797183488,58568849408953271491175730111341133823,
    '2c0f:f338::/32',327983,N'Interworks-Wireless-Solutions');

DECLARE @ip numeric(38,0) = 58568842991472107835764385034281156617;

SELECT iplow,iphigh,c,d,e
  FROM #ips 
  WHERE @ip BETWEEN iplow AND iphigh;

DROP TABLE #ips;

If this doesn't work for the addresses you need to support then you may need to separate out the network prefix (48 bits), subnet ID (16 bits), and interface ID (64 bits) into three separate numbers before passing them off to SQL Server. Your where clauses would become more complex but this might be a simpler approach than trying to represent an IPv6 as a single number anyway.

Is there a way to properly handle IP address ranges in SqlServer ?

SQL Server has no built-in capability to understand what an IP address is.