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.