|
|
我有一个存储表ID，Name，Code，IPLow，IPHigh如：
9 s7 b) L/ T! M9 d2 g; S' H: y; r1,Lucas,804645,192.130.1.1,192.130.1.2542,Maria,222255,192.168.2.1,192.168.2.2543,Julia,123456,192.150.3.1,192.150.3.254现在，如果我有的话IP地址192.168.2.50.如何检索匹配记录？6 p: A% Z' G6 b
编辑
& V$ w; w7 ]5 Y1 D# H根据戈登的回答(我遇到了编译错误)，这就是我的想法:7 c& _9 a+ @, E: f2 x# S
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 ) PersonnelPCwhere 192168002050 between iplow_decimal and iphigh_decimal;但这给了我一个错误：
, N+ P) o+ T2 k" ^Msg 8115,Level 16,State 2,Line 1Arithmetic overflow error converting expression to data type int.有什么想法吗？0 h- K2 S5 }' Z
1 }# @- p1 M2 L a4 f 解决方案: 3 ]2 S Z7 h( K0 J. ]! r# j/ S
痛苦的。SQL Server字符串操作功能差。但是，它提供了parsename()。这种方法将是IP将地址转换为较大的十进制值进行比较：, |. D3 T- G! J. s9 @+ C
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 ) twhere 192168002050 between iplow_decimal and iphigh_decimal;要注意，IP地址通常以4字节无符号整数的形式存储在数据库中。这要容易得多。。。尽管您需要复杂的逻辑（通常包装在函数中）来将值转换为可读格式。 |
|
技术问答
208 人阅读
|
0 人回复
|
2023-09-14
