Select record between two IP ranges

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.


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.

Tags:

Sql

Sql Server