今天在 CSDN 上遇到这么一个问题:在数据库中有字段 StartIP、EndIP、Country 和 Local等,分别表示起始 IP、结束 IP、国家和地区等,现在给出一个 IP,让我们取其 Country 和 Local。
SQL 语句的 WHERE 条件如下:
where '218.18.159.177' Between startIP and EndIP
但查出的结果中包含了不想要的结果,比如 218.176.0.0(StartIP)、218.183.255.255(EndIP)是不符合结果的,却被查出来了。
BETWEEN 可以比较数字、文本、日期,这里是文本。
考察:218.176. 与条件 218.18.
先比较第一个字符相等,第二个也相等,直到第六个,发现 7 < 8,符合条件,就选出来了。
解决办法:
格式化 IP,全部格式化为 16 位,比如:218.176.000.000、18.018.158.000。
进入 WHERE 的 218.18.159.177 也要格式化为 218.018.159.177。
我写了一个格式化数据库中 IP 的 T-SQL 代码,放在查询分析器中运行一下就可以把 StartIP 改为 16 位格式了。
declare cr cursor for select StartIP from Tab_IP
open cr
declare @StartIP varchar(16)
fetch next from cr into @StartIP
while @@fetch_status=0
begin
declare @NewStartIP varchar(16)
declare @pos0 int, @pos1 int, @pos2 int, @pos3 int, @pos4 int
select @pos0 = 0
select @pos1 = charindex('.', @StartIP, @pos0 + 1)
select @pos2 = charindex('.', @StartIP, @pos1 + 1)
select @pos3 = charindex('.', @StartIP, @pos2 + 1)
select @pos4 = Len(@StartIP) + 1
select @NewStartIP = replicate('0', 3 - (@pos1 - @pos0 - 1)) + substring(@StartIP, @pos0 + 1, @pos1-@pos0) +
replicate('0', 3 - (@pos2 - @pos1 - 1)) + substring(@StartIP, @pos1 + 1, @pos2-@pos1) +
replicate('0', 3 - (@pos3 - @pos2 - 1)) + substring(@StartIP, @pos2 + 1, @pos3-@pos2) +
replicate('0', 3 - (@pos4 - @pos3 - 1)) + substring(@StartIP, @pos3 + 1, @pos4-@pos3)
update Tab_IP set StartIP=@NewStartIP where StartIP=@StartIP
fetch next from cr into @StartIP
end
close cr
deallocate cr