Select record between two IP ranges

Question

I have a table which stores a ID, Name, Code, IPLow, IPHigh such as:

1, Lucas, 804645, 192.130.1.1, 192.130.1.254
2, Maria, 222255, 192.168.2.1, 192.168.2.254
3, Julia, 123456, 192.150.3.1, 192.150.3.254

Now, if I have an IP address 192.168.2.50, how can I retrieve the matching record?

Edit

Based on Gordon's answer (which I'm getting compilation errors) this is what I have:

select PersonnelPC.*
from (select PersonnelPC.*,
             (
              cast(parsename(iplow, 4)*1000000000 as decimal(12, 0)) +
              cast(parsename(iplow, 3)*1000000 as decimal(12, 0)) +
              cast(parsename(iplow, 2)*1000 as decimal(12, 0)) +
              (parsename(iplow, 1))
             ) as iplow_decimal,
            (
              cast(parsename(iphigh, 4)*1000000000 as decimal(12, 0)) +
              cast(parsename(iphigh, 3)*1000000 as decimal(12, 0)) +
              cast(parsename(iphigh, 2)*1000 as decimal(12, 0)) +
              (parsename(iphigh, 1))
             ) as iphigh_decimal
      from PersonnelPC
     ) PersonnelPC
where 192168002050 between iplow_decimal and iphigh_decimal;

but this gives me an error:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Any ideas?

Please describe your efforts so far and the results of those efforts. — Dan Bracuk, Mar 23 '16 at 11:56
You store a human friendly textual representation of the addresses and there is no built in comparison that understand IP range semantics in that format. Use a numeric comparison instead, e.g. [How to search efficiently for IP addresses ranges?](http://stackoverflow.com/questions/33510513/how-to-search-efficiently-for-ip-addresses-ranges) — Alex K., Mar 23 '16 at 11:58
Just be sure to test the cases where subnets are not only /24 — Kieveli, Mar 23 '16 at 12:25
Cast the large value to BIGINT to avoid the arithmetic overflow error. Better yet, cast it to decimal(12,0) for consistency. (cast(192168002050 as decimal(12,0))) — EduardoCMB, Mar 23 '16 at 14:00

Gordon Linoff · Accepted Answer · 2016-03-23T14:02:03.510

8

Painfully. SQL Server has lousy string manipulation functions. It does, however, offer parsename(). This approach converts the IP address to a large decimal value for the comparison:

select t.*
from (select t.*,
             (cast(parsename(iplow, 4)*1000000000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 3)*1000000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 2)*1000.0 as decimal(12, 0)) +
              cast(parsename(iplow, 1) as decimal(12, 0))
             ) as iplow_decimal,
             (cast(parsename(iphigh, 4)*1000000000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 3)*1000000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 2)*1000.0 as decimal(12, 0)) +
              cast(parsename(iphigh, 1) as decimal(12, 0))
             ) as iphigh_decimal
      from t
     ) t
where 192168002050 between iplow_decimal and iphigh_decimal;

I should note that IP addresses are often stored in the database as the 4-byte unsigned integers. This makes comparisons much easier . . . although you need complicated logic (usually wrapped in a function) to convert the values to a readable format.

edited Mar 23 '16 at 14:02

answered Mar 23 '16 at 11:58

Gordon Linoff

1,242,037
58
646
786

2

Interesting use of base 10 conversion to leave it readable (I've used 4x4-byte longs to store IPs). The casts will make this slow - consider storing the IP as non-human readable, or alternatively, add a trigger to update a second field storing the long value matching the human readable IP address. – Kieveli Mar 23 '16 at 12:22
1

@Gordon - I have made a change which I think gets around the OP's problem with implicit conversion to `int`. Please revert if it's incorrect. – Ed Harper Mar 23 '16 at 13:41
@ EdHarper could you please post your suggestion? – alwaysVBNET Mar 23 '16 at 13:48
1

@alwaysVBNET - I already have; the current version of this answer should contain my edit (which is to add `.0` to the scalar multipliers) – Ed Harper Mar 23 '16 at 13:54
@EdHarper great help – alwaysVBNET Mar 23 '16 at 14:05
@GordonLinoff great answer – alwaysVBNET Mar 23 '16 at 14:06
Actually, no need to multiply as big as `1,000,000,000 = 1,000**3` -- you can use `256**3` (about 17 million, about 1% as large), `256**2`, `256` instead – MichaelChirico Mar 08 '18 at 10:34

score 6 · Answer 2 · edited Mar 23 '16 at 14:21

6

Try this simple way checking range

DECLARE @IP NVARCHAR(30)='192.168.500.1'

SELECT  * FROM 
Branches
WHERE
CAST (PARSENAME(@IP,4) AS INT)>=CAST(PARSENAME(IPLow,4) AS INT) AND CAST(PARSENAME(@IP,3) AS INT)>=CAST(PARSENAME(IPLow,3) AS INT) AND CAST(PARSENAME(@IP,2) AS INT)>=CAST(PARSENAME(IPLow,2) AS INT) AND CAST(PARSENAME(@IP,1) AS INT)>=CAST(PARSENAME(IPLow,1) AS INT)
AND
CAST(PARSENAME( @IP,4) AS INT) <= CAST(PARSENAME(IPHigh ,4) AS INT) AND CAST(PARSENAME(@IP ,3) AS INT) <=CAST(PARSENAME(IPHigh ,3) AS INT) AND CAST(PARSENAME(@IP ,2) AS INT) <=CAST(PARSENAME(IPHigh ,2) AS INT) AND CAST(PARSENAME(@IP ,1) AS INT)<=CAST(PARSENAME(IPHigh ,1) AS INT)

AS Per @Ed Haper Comment Cast is needed.

edited Mar 23 '16 at 14:21

alwaysVBNET

3,150
8
32
65

answered Mar 23 '16 at 13:01

Jaydip Jadhav

12,179
6
24
40

it won't fetch any results – alwaysVBNET Mar 23 '16 at 13:04
1

In its current form, this won't work because `PARSENAME` returns strings, and in a string comparison, `50` > `254`. You'd have to cast the output of all the calls to `PARSENAME` as a numeric type - perhaps `int` - to get the comparison to work properly. Plus - are you sure your comparisons are the right way round? – Ed Harper Mar 23 '16 at 13:35
@JaydipJ Sorry, it doesn't work. Try '192.168.130.2' in a range '192.168.130.1' - '192.168.130.254' – alwaysVBNET Mar 23 '16 at 13:55
@alwaysVBNET I have Updated as per EdHarper Comment – Jaydip Jadhav Mar 23 '16 at 14:12
@JaydipJ works with some parenthesis fixing. Good answer +1 – alwaysVBNET Mar 23 '16 at 14:19

Shnugo · Answer 3 · 2016-03-23T17:13:26.707

With this function you can transform any IP address to a form where each part has 3 digits. With this you could do a normal alphanumeric compare. if you want you could return BIGINT too...

CREATE FUNCTION dbo.IPWidth3(@IP VARCHAR(100))
RETURNS VARCHAR(15)
BEGIN
DECLARE @RetVal VARCHAR(15);
WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@IP,'.','</x><x>') + '</x>' AS XML) AS IPSplitted 
)
SELECT @RetVal = STUFF(
        (
        SELECT '.' + REPLACE(STR(Part.value('.','int'),3),' ','0')
        FROM Splitted.IPSplitted.nodes('/x') AS One(Part)
        FOR XML PATH('')
        ),1,1,'') 
FROM Splitted;

RETURN @RetVal;
END
GO

DECLARE @IP VARCHAR(100)='192.43.2.50';
SELECT dbo.IPWidth3(@IP);

The result

192.043.002.050

To reflect Ed Harper's comment here the same function returning a DECIMAL(12,0):

CREATE FUNCTION dbo.IP_as_Number(@IP VARCHAR(100))
RETURNS DECIMAL(12,0)
BEGIN
DECLARE @RetVal DECIMAL(12,0);
WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(@IP,'.','</x><x>') + '</x>' AS XML) AS IPSplitted 
)
SELECT @RetVal = 
        CAST((
        SELECT REPLACE(STR(Part.value('.','int'),3),' ','0')
        FROM Splitted.IPSplitted.nodes('/x') AS One(Part)
        FOR XML PATH('')
        ) AS DECIMAL(12,0))
FROM Splitted;

RETURN @RetVal;
END
GO

DECLARE @IP VARCHAR(100)='192.43.2.50';
SELECT dbo.IP_as_Number(@IP);

If you use this approach, it's worth being aware that .Net `IPAddress.Parse` method will interpret sections of an IPv4 address padded with leading zeroes as octal values - so you need to be careful how these values are exposed to consuming code. This might affect other languages. — Ed Harper, Mar 23 '16 at 13:39
@EdHarper, thx, did not know this... I edited my answer and added a version returning with `DECIMAL(12,0)` — Shnugo, Mar 23 '16 at 17:14

Uday · Answer 4 · 2016-03-23T13:31:40.700

Use below to fetch the ipLow / IPHigh in 4 columns. You can use those columns to compare Ips.

DECLARE@ip VARCHAR(50)='192.168.0.81' 
SELECT (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))

,
substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
''))))),
SUBSTRING((SUBSTRING(@ip, LEN((SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))) + 2 + LEN(substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')))))) + 1,
LEN(@IP) - 1 - LEN(reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))))), 0,
PATINDEX('%.%',
(SUBSTRING(@ip, LEN((SUBSTRING((@ip), 0,
patindex('%.%',
(@ip))))) + 2 + LEN(substring((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')),
0,
patindex('%.%',
((REPLACE(@ip, (SUBSTRING((@ip), 0,
patindex('%.%',
(@ip)) + 1)),
'')))))) + 1,
LEN(@IP) - 1 - LEN(reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))))

))),
reverse(SUBSTRING(reverse(@ip), 0,
patindex('%.%',
reverse(@ip))))

Frank McCoy · Answer 5 · 2016-03-24T18:37:49.137

Consider something like this example to convert the address into a number.

CREATE FUNCTION dbo.IPAddressAsNumber (@IPAddress AS varchar(15))
RETURNS bigint
BEGIN
RETURN
 CONVERT (bigint,
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 4))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 3))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 2))) +
  CONVERT(varbinary(1), CONVERT(int, PARSENAME(@IPAddress, 1))) )
END

and with that you could use standard operators like BETWEEN to find rows within the range you have in the table

DECLARE @t table (ID int, Name varchar(50), Code int, IPLow varchar(15), IPHigh varchar(15))
INSERT INTO @t VALUES 
 (1, 'Lucas', 804645, '192.130.1.1', '192.130.1.254'),
 (2, 'Maria', 222255, '192.168.2.1', '192.168.2.254'),
 (3, 'Julia', 123456, '192.150.3.1', '192.150.3.254')

SELECT * FROM @t
WHERE dbo.IPAddressAsNumber('192.168.2.50')
 BETWEEN dbo.IPAddressAsNumber(IPLow) AND dbo.IPAddressAsNumber(IPHigh)

The scheme essentially uses PARSENAME to isolate each part of the address, converts each part into a SQL binary string, concatenating the strings together to get a single SQL binary string representing the address, and shows the result as a bigint.

In a textual representation of hexadecimal values think of this as smashing the 4 parts together 192(0xC0) + 168(0xA8) + 2(0x02) + 50(0x32) into 0xC0A80232. When you turn that combined string into its binary digits (0s and 1s) you would end up with something that could be thought of as the address in a binary form used by the network stack in address routing and subnet masking tables. When you turn that into a number in the form of an unsigned integer (or in this case a bigint) you get 3232236082.

Interestingly this scheme gives you a "number" that can be used in place of the original address in lots of ways. You can for example ping the number 2130706433 instead of the address 127.0.0.1 -- the name resolver in Windows will convert it similarly to how DNS is used to find the address of a hostname.

For the sake of completeness, here is another function that can be used to convert the number form back into the standard string form

CREATE FUNCTION dbo.IPAddressFromNumber (@IPNumber AS bigint)
RETURNS varchar(15)
BEGIN
RETURN
 CONVERT (varchar(15),
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 1,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 2,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 3,1))) + '.' +
  CONVERT(varchar(3), CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @IPNumber), 4,1))) )
END

score 0 · Answer 6 · answered May 24 '19 at 09:52

select *
from ip a
join ip_details b
on a.ip_address >= b.ip_start
and a.ip_address <= b.ip_end;

In this, table "a" contains list of IP address and table "b" contains the IP ranges.

Instead of converting the ip address to numeric we can directly compare the string, it will do a byte by byte comparison.

This is working for me(PostgreSQL).

score 0 · Answer 7 · answered Apr 24 '20 at 20:19

I was thinking along the lines of Gordon's answer, then realized you don't actually need to mess with numbers. If you zero-pad each part of the address, a string comparison works:

DECLARE @search varchar(50) = '192.168.2.50';
WITH DATA AS (
    SELECT * FROM ( values 
            (1, 'Lucas', '192.130.1.1', '192.130.1.254'),
            (2, 'Maria', '192.168.2.1', '192.168.2.254'),
            (3, 'Julia', '192.150.3.1', '192.150.3.254')
    ) AS tbl (ID,Name,IPLow,IPHigh)
)
SELECT *
FROM DATA
WHERE REPLACE(STR(PARSENAME( @search, 4 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( @search, 3 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( @search, 2 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( @search, 1 ), 3, 0), ' ', '0')

    BETWEEN

      REPLACE(STR(PARSENAME( IPLow, 4 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPLow, 3 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPLow, 2 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPLow, 1 ), 3, 0), ' ', '0')

    AND

      REPLACE(STR(PARSENAME( IPHigh, 4 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPHigh, 3 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPHigh, 2 ), 3, 0), ' ', '0')
    + REPLACE(STR(PARSENAME( IPHigh, 1 ), 3, 0), ' ', '0')

You can, of course, put this inside a UDF for simplicity, though watch out for the performance hit on large queries.

CREATE FUNCTION dbo.IP_Comparable(@IP varchar(50))
RETURNS varchar(50)
WITH SCHEMABINDING
BEGIN
    RETURN REPLACE(STR(PARSENAME( @IP, 4 ), 3, 0), ' ', '0')
         + REPLACE(STR(PARSENAME( @IP, 3 ), 3, 0), ' ', '0')
         + REPLACE(STR(PARSENAME( @IP, 2 ), 3, 0), ' ', '0')
         + REPLACE(STR(PARSENAME( @IP, 1 ), 3, 0), ' ', '0')
END
GO

DECLARE @search varchar(50) = '192.168.2.50';
WITH DATA AS (
    SELECT * FROM ( values 
        (1, 'Lucas', '192.130.1.1', '192.130.1.254'),
        (2, 'Maria', '192.168.2.1', '192.168.2.254'),
        (3, 'Julia', '192.150.3.1', '192.150.3.254')
    ) AS tbl (ID,Name,IPLow,IPHigh)
)
SELECT *
FROM DATA
WHERE dbo.IP_Comparable(@search) BETWEEN dbo.IP_Comparable(IPLow) AND dbo.IP_Comparable(IPHigh)

This will avoid the issue you're having with integer overflows.

score -3 · Answer 8 · answered Mar 23 '16 at 11:56

-3

Depends on which record you are looking for the high or the low.

select * from table where IPlow like '192.168.2.50' or IPHigh like '192.168.2.50'

answered Mar 23 '16 at 11:56

GabrielVa

2,353
9
37
59

Obviously the range is not known and should be searched from what is available in the data. Otherwise, it would have been too easy. – alwaysVBNET Mar 23 '16 at 11:59
2

Downvoted because this approach will not solve the problem. – Dan Bracuk Mar 23 '16 at 12:01

Select record between two IP ranges

8 Answers8

Linked