SQL SERVER对字符串进行分割

方法一:
select @a1=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a2=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a3=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))

方法二:
declare @ip varchar(20),@a int,@b int,@c int ,@d int;
select @ip='192.168.0.5';
select @a = charIndex('.',@ip);
select @b =charIndex('.',@ip,@a+1);
select @c=charIndex('.',@ip,@b+1);

select substring(@ip,1,@a-1)
select substring(@ip,@a+1,@b-@a-1)
select substring(@ip,@b+1,@c-@b-1)
select substring(@ip,@c+1,len(@ip))

参考地址:http://www.cnblogs.com/TopWin/archive/2006/05/11/397273.html
alter proc lockip_list
@ip varchar(100)
as
declare @val varchar(50)
declare @value varchar(50)
declare @a1 varchar(50)
declare @a2 varchar(50)
declare @a3 varchar(50)
declare @a4 varchar(50)
declare @i int
select @i=0
select @a1=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a2=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a3=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a4=@ip
select * from lockip where @a1=substring(ipstart,0,charindex('.',ipstart,@i)) and @a2=substring(ipstart,charindex('.',ipstart,@i)+1,charindex('.',ipstart,@i+1)-charindex('.',ipstart,@i+1)-1)

exec lockip_list '60.190.24.230'
改过的方法:
alter proc lockip_list
@ip varchar(100)
as
declare @val varchar(50)
declare @value varchar(50)
declare @a1 varchar(50)
declare @a2 varchar(50)
declare @a3 varchar(50)
declare @a4 varchar(50)
declare @i int
select @i=0
select @a1=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a2=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a3=left(@ip,charindex('.',@ip)-1)
select @ip=right(@ip,len(@ip)-charindex('.',@ip))
select @a4=@ip
select * from ip where @a1=substring(ipstart,0,charindex('.',ipstart,@i)) and  @a2=substring(ipstart,charindex('.',ipstart,@i)+1,charindex('.',ipstart,charindex('.',ipstart,@i)+1)-charindex('.',ipstart,@i)-1)