CREATE function f_splitstr(
@s varchar(8000), ---字符串
@split varchar(20) ---分隔符
)returns @re table(col varchar(100))
as
begin
declare @splitlen int
set @splitlen=len(@split+'a')-1
while charindex(@split,@s)>0
begin
insert @re values(left(@s,charindex(@split,@s)-1))
set @s = stuff(@s,1,charindex(@split,@s)-1+@splitlen,'')
end
insert @re values(@s)
Return
End
-----调用
select * from dbo.f_splitstr('1,2,3',',')
@s varchar(8000), ---字符串
@split varchar(20) ---分隔符
)returns @re table(col varchar(100))
as
begin
declare @splitlen int
set @splitlen=len(@split+'a')-1
while charindex(@split,@s)>0
begin
insert @re values(left(@s,charindex(@split,@s)-1))
set @s = stuff(@s,1,charindex(@split,@s)-1+@splitlen,'')
end
insert @re values(@s)
Return
End
-----调用
select * from dbo.f_splitstr('1,2,3',',')
declare @str varchar(800)
declare @sql varchar(8000)
set @str ='1,2,3' ---字符串
set @sql=''
set @sql='select col = '''+ replace(@str,',','''union all select ''')
+''''exec(@sql)
declare @str varchar(800)
declare @sql varchar(8000)
set @str ='1,2,3' ---字符串
set @sql=''
set @sql='select col = '''+ replace(@str,',','''union all select ''')
+''''exec(@sql)