22,207
社区成员
发帖
与我相关
我的任务
分享
Declare @str varchar(1000)
SET @str='aaa,bb,ccc,d,ee,ffff'
set @str=replace(@str,',',''' union all select ''')
exec('select col=''' + @str+'''')
--学楼主把@弄掉了
/*------------------------
Declare @str varchar(100)
SET @str='aaa,bb,ccc,d,ee,ffff'
while charindex(',',@str)>0
begin
print left(@str,charindex(',',@str)-1)
--left(@str,charindex(',',str)-1)这块就是循环截取下来的每一部分,对其进行操作就可以了
set @str = stuff(@str,1,charindex(',',@str),'')
end
------------------------*/
aaa
bb
ccc
d
ee
if object_id('dbo.fn_split')is not null drop function dbo.fN_split
go
create function dbo.fn_split --創建函數
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用函數
declare @s varchar(1000)
set @s='aaa,bb,ccc,d,ee,ffff'
select * from dbo.fn_split(@s,',')
/*
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aaa
bb
ccc
d
ee
ffff
(影響 6 個資料列)
*/
Declare @str varchar(100)
SET @str='aaa,bb,ccc,d,ee,ffff'
select top 2000 id=identity(int,1,1) into # from syscolumns a,syscolumns b
select
iNewDesc=substring(a.iDesc,b.id,charindex(',',a.iDesc+',',b.id)-b.id)
from (select @str as idesc ) a, # b
where substring(','+a.iDesc,b.id,1)=','
order by a.idesc,b.id
drop table #
/*
iNewDesc
----------------------------------------------------------------------------------------------------
aaa
bb
ccc
d
ee
ffff
(所影响的行数为 6 行)
*/
Declare @str varchar(100)
SET @str='aaa,bb,ccc,d,ee,ffff'
while charindex(',',str)>0
begin
print left(@str,charindex(',',str)-1)
--left(@str,charindex(',',str)-1)这块就是循环截取下来的每一部分,对其进行操作就可以了
set @str = stuff(@str,1,charindex(',',str),'')
end