例如把"aaaa,bbb"按照","分离成一个数组
---------------------------------------------------------------
昨天做的函数，不过效率可能不高，如果可能还是建议在客户端做。
：）
drop function getstrofindex
go
create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1 --如果习惯从0开始则select @next =0
select @location = charindex(',',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1 --如果是因为没有逗号退出，则认为逗号在字符串后
select @str_return = substring(@str,@start,@location -@start) --@start肯定是逗号之后的位置或者就是初始值1
if (@index <> @next ) select @str_return = '' --如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。
return @str_return
end
go
select dbo.getstrofindex('1435,2523,,44325,',)
---------------------------------------------------------------
declare @string varchar(1000)
set @string='aaaa,bbbb,cccc,dddd'
set @string='select * into ##tmp from (select '''+replace(@string,',','''as str union all select ''')+''') a'
exec(@string)
insert 表 select * from ##tmp where str not in (select 列 from 表)
drop table ##tmp