create function f_splitStr(@s varchar(8000),@split varchar(10))
returns @tb table(col varchar(100))
as
begin
declare @t table(id int identity,b bit)
insert @t select top 100 0 from syscolumns a,syscolumns b
insert @tb select substring(@s,id,charindex(@split,@s+@split,id)-id)
from @t
where id<=len(@s+'!') and charindex(@split,@split+@s,id)=id
return
end
go
select * from dbo.f_splitStr('测试文件1,测试2,测试件3',',')
drop function f_splitStr
create function fun_getStr(@a nvarchar(100))
returns @t table(a nvarchar(100))
as
begin
set @a=@a+','
while charindex(',',@a)>0
begin
insert @t select left(@a,charindex(',',@a)-1)
set @a=right(@a,len(@a)-charindex(',',@a))
end
return
end
go
declare @sql varchar(8000)
set @sql = ''
select @sql = replace(field,',',''' union all select ''') from #t
set @sql = 'select '' ' + REVERSE(stuff(REVERSE(@sql),3,16,''))
set @sql = REVERSE(stuff(REVERSE(@sql),1,1,''))
print @sql
exec(@sql)
declare @tb table
(
F1 varchar(8000)
)
declare @str varchar(8000),@st1 varchar(8000),@I int
select @str='测试文件1,测试2,测试件3,'
select @I=charindex(',',@str)
while @I > 0
begin
insert into @tb
select substring(@str,1,@I-1)
select @str=substring(@str,@I+1,8000)
select @I=charindex(',',@str)
end
select * from @tb