我有一张表格,其中存储了ID
、Name
、Code
、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, 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;
但是这会给我一个错误:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
有任何想法吗?