Sqlserver IPV6 handling function or numeric conversion

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 th

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.

StackExchange Database Administrators Q#215477, answer score: 2