# Is IP Address in this range?

• Rich Cowan

Old Hand

Points: 306

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

• SwePeso

SSC-Dedicated

Points: 39693

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"

• SwePeso

SSC-Dedicated

Points: 39693

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"

• Rich Cowan

Old Hand

Points: 306

Thank you for the help.  This is exactly what I was after.

R.

Viewing 4 posts - 1 through 4 (of 4 total)