August 16, 2006 at 8:00 am
Does anyone have a SQL function to determine if a given IP address is in a specific IP range?
Any help is greatly appreciated.
Rich
August 17, 2006 at 5:36 am
Easy. Write like this
SELECT CASE
WHEN dbo.fnIPv4('193.194.227.51') BETWEEN dbo.fnIPv4('193.194.226.19') AND dbo.fnIPv4('194.193.1.1') THEN 'Yes'
ELSE 'No'
END
Where the function dbo.fnIPv4 looks like this
CREATE FUNCTION dbo.fnIPv4
(
@IP VARCHAR(15)
)
RETURNS BIGINT
AS
BEGIN
RETURN 16777216 * CAST(PARSENAME(@IP, 4) AS BIGINT) +
65536 * CAST(PARSENAME(@IP, 3) AS BIGINT) +
256 * CAST(PARSENAME(@IP, 2) AS BIGINT) +
CAST(PARSENAME(@IP, 1) AS BIGINT)
END
N 56°04'39.16"
E 12°55'05.25"
August 17, 2006 at 6:42 am
Or more specialized.
CREATE FUNCTION dbo.fnIPv4_InRange
(
@CurrentIP VARCHAR(15),
@FromIP VARCHAR(15),
@ToIP VARCHAR(15)
)
RETURNS BIT
AS
BEGIN
DECLARE @CurrentNum BIGINT,
@FromNum BIGINT,
@ToNum BIGINT,
@InRange BIT
SELECT @CurrentNum = 16777216 * CAST(PARSENAME(@CurrentIP, 4) AS BIGINT) +
65536 * CAST(PARSENAME(@CurrentIP, 3) AS BIGINT) +
256 * CAST(PARSENAME(@@CurrentIP, 2) AS BIGINT) +
CAST(PARSENAME(@CurrentIP, 1) AS BIGINT),
@FromNum = 16777216 * CAST(PARSENAME(@FromIP, 4) AS BIGINT) +
65536 * CAST(PARSENAME(@CurrentIP, 3) AS BIGINT) +
256 * CAST(PARSENAME(@CurrentIP, 2) AS BIGINT) +
CAST(PARSENAME(@CurrentIP, 1) AS BIGINT),
@ToNum = 16777216 * CAST(PARSENAME(@ToIP, 4) AS BIGINT) +
65536 * CAST(PARSENAME(@ToIP, 3) AS BIGINT) +
256 * CAST(PARSENAME(@ToIP, 2) AS BIGINT) +
CAST(PARSENAME(@ToIP, 1) AS BIGINT)
IF @FromNum < @ToNum
SELECT @InRange = CASE WHEN @CurrentNum BETWEEN @FromNnum AND @ToNum THEN 1 ELSE 0 END
ELSE
SELECT @InRange = CASE WHEN @CurrentNum BETWEEN @ToNnum AND @FromNum THEN 1 ELSE 0 END
RETURN @InRange
END
Call with
select dbo.fnIPv4_InRange('193.194.227.51', '193.194.226.19', '194.193.1.1')
or
select dbo.fnIPv4_InRange('193.194.227.51', '194.193.1.1', '193.194.226.19')
N 56°04'39.16"
E 12°55'05.25"
August 17, 2006 at 9:33 am
Thank you for the help. This is exactly what I was after.
R.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply