你的位置:首页 > 数据库

[数据库]IP地址转化为数字,charindex ,SUBSTRING


 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

返回前台所查到的结果:

USE [91hurong]GO/****** Object: StoredProcedure [dbo].[ProIP]  Script Date: 08/13/2015 08:40:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[ProIP]  -- Add the parameters for the stored procedure here  @str varchar(50)ASBEGIN   declare @IPStart nvarchar(255), --存放传入的数据    @diana int,  --存放点    @dianb int,    @dianc int,       @liea bigint,  -- 存放列      @lieb bigint,    @liec bigint,    @lied bigint,       @intlon bigint  --存放化为的整数  set @IPStart= @str;        --传入的IP'1.11.6.6'   -- set @IPStart= '1.11.6.6';   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) --拼为整数      declare @inta bigint   set @inta=(select top 1 IP3  from I_IPCity where IP3>@intlon and IPStart is not null and IPID is not null and IP3 is not null order by IP3 asc)   select top 1 * from I_IPCity where IP3<@inta and IPStart is not null and IPID is not null and IP3 is not null order by IP3 desc  --返回'61.177.117.6'在表中对应的数据 
END
exec ProIP '61.177.117.6'

说明:

 'Arg.ea' 对应位置:1 2 3 4 5 6 charindex('.','Arg.ea') >0  --如果大于零,则表示字符串Area中含有字符串CityName; 此例为true charindex('.','Arg.ea',2 ) -- 从第二个位置后,也就是从 字母'r'后开始找,先判断 ‘g’是否为‘.’ ,为否;继续判断‘.’是否为‘.’,此表达式为true SUBSTRING('Arg.ea',1,2) --截取 字符串 'Arg.ea'中 第一个位置到第二个位置 ;也就是‘Ar’