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’