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 /* F1 ------- 测试文件1 测试2 测试件3 */
create table #t (field varchar(1000)) insert #t select '测试文件1,测试2,测试件3,' 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)drop table #t
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 goselect * from dbo.fun_getStr('测试文件1,测试2,测试件3')
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_splitStrcol ---------- 测试文件1 测试2 测试件3
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
/*
F1
-------
测试文件1
测试2
测试件3
*/
insert #t
select '测试文件1,测试2,测试件3,' 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)drop table #t
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
goselect * from dbo.fun_getStr('测试文件1,测试2,测试件3')
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_splitStrcol
----------
测试文件1
测试2
测试件3