Skip to content

Instantly share code, notes, and snippets.

@phdesign
Created June 5, 2018 04:12
Show Gist options
  • Save phdesign/e899c7536375ad0d373262226c0d00ec to your computer and use it in GitHub Desktop.
Save phdesign/e899c7536375ad0d373262226c0d00ec to your computer and use it in GitHub Desktop.
Check if a string IP address is in a CIDR range in SQL Server
create function dbo.IPAddressIsInRange(@ip as varchar(15), @range as varchar(18))
returns bit
as
begin
declare @prefix varchar(15),
@cidr varchar(2),
@mask bigint
set @prefix = left(@range, charindex('/', @range) - 1)
set @cidr = right(@range, len(@range) - charindex('/', @range))
-- Converts to a bit mask, e.g. /24 = 255.255.255.0
set @mask = 4294967295 - power(2, 32 - @cidr) + 1
if (dbo.IPAddressToInteger(@ip) & @mask) = dbo.IPAddressToInteger(@prefix)
return 1
return 0
end
go
-- Credit: http://sqlblog.com/blogs/denis_gobo/archive/2008/10/05/9266.aspx
create function dbo.IPAddressToInteger(@ip as varchar(15))
returns bigint
as
begin
return (
convert(bigint, parsename(@ip, 1)) +
convert(bigint, parsename(@ip, 2)) * 256 +
convert(bigint, parsename(@ip, 3)) * 65536 +
convert(bigint, parsename(@ip, 4)) * 16777216
)
end
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment