我有一个表存储一个ID,名称,代码,IPLow,IPHigh如：
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
现在,如果我有一个IP地址192.168.2.50,我如何检索匹配的记录？
编辑
基于Gordon的答案(我正在收到编译错误)这是我所拥有的：
select PersonnelPC.*
from (select PersonnelPC.*,(
cast(parsename(iplow,4)*1000000000 as decimal(12,0)) +
cast(parsename(iplow,3)*1000000 as decimal(12,2)*1000 as decimal(12,0)) +
(parsename(iplow,1))
) as iplow_decimal,(
cast(parsename(iphigh,0)) +
cast(parsename(iphigh,0)) +
(parsename(iphigh,1))
) as iphigh_decimal
from PersonnelPC
) PersonnelPC
where 192168002050 between iplow_decimal and iphigh_decimal;
但这给我一个错误：
Msg 8115,Level 16,State 2,Line 1 Arithmetic overflow error converting expression to data type int.
有任何想法吗？
解决方法
痛苦. sql Server具有糟糕的字符串操作功能.但是,它提供了parsename().该方法将IP地址转换为较大的十进制值进行比较：
select t.*
from (select t.*,(cast(parsename(iplow,4)*1000000000.0 as decimal(12,3)*1000000.0 as decimal(12,2)*1000.0 as decimal(12,1) as decimal(12,0))
) as iplow_decimal,(cast(parsename(iphigh,0))
) as iphigh_decimal
from t
) t
where 192168002050 between iplow_decimal and iphigh_decimal;
我应该注意,IP地址通常作为4字节的无符号整数存储在数据库中.这使得比较更容易. . .虽然您需要复杂的逻辑(通常包装在一个函数中)来将值转换为可读格式.
