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)

You must be logged in to reply to this topic. Login to reply