IP地址转化为数目字，charindex ，SUBSTRING

www.MyException.Cn  网友分享于：2015-08-15  浏览：0次
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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE  [dbo].[ProIP]
-- Add the parameters for the stored procedure here
@str  varchar(50)
AS
BEGIN

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’