SET NOCOUNT ON; declare @I_PCity table ( IPStart nvarchar(255), Area nvarchar(255), CityID int, IPID int ) declare @IPS ...
SET NOCOUNT ON; declare @I_PCity table ( IPStart nvarchar(255), Area nvarchar(255), CityID int, IPID int ) declare @IPStart nvarchar(255), @diana int, --存放点 @dianb int, @dianc int, @liea bigint, -- 存放列 @lieb bigint, @liec bigint, @lied bigint, @intlon bigint --存放化为的整数 insert into @I_PCity(IPStart, Area, CityID, IPID ) select IPStart, Area, CityID, IPID from I_IPCity (nolock) where IPID>= 430000 and IPStart is not null and IP3 is null -- while( exists(select top 1 * from @I_PCity)) begin set @IPStart= (select top 1 IPStart from @I_PCity ); set @diana= charindex('.',@IPStart) --第一个点的下标 set @dianb= charindex('.',@IPStart, @diana +1 ) --第二个点的下标 set @dianc= charindex('.',@IPStart, @dianb +1 ) --第三个点的下标 set @liea = SUBSTRING(@IPStart,1,@diana-1) --第一列的值 set @lieb =SUBSTRING(@IPStart,@diana+1,@dianb-@diana-1 )--第二列的值 set @liec =SUBSTRING(@IPStart,@dianb+1,@dianc-@dianb-1 )--第三列的值 set @lied =SUBSTRING(@IPStart,@dianc+1, LEN(@IPStart))--第四列的值 set @intlon= @lied+(@liec*1000)+(@lieb*1000000)+(@liea*1000000000) --拼为整数 update I_IPCity set IP3 =@intlon where IPID= (select top 1 IPID from @I_PCity) delete top(1) from @I_PCity end
原标题:IP地址转化为数字,charindex ,SUBSTRING
关键词:string
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。