34,576
社区成员
发帖
与我相关
我的任务
分享
declare @str nvarchar(1000)
if object_id('tempdb..#t') is not null
drop table #t
create table #t (fstr varchar(20))
set @str='a,b,c,d,e'
set @str=replace(@str,',',''')insert into #t values(''')
select @str='insert into #t values('''+@str+''')'
exec(@str)
select * from #t
declare @str nvarchar(100),@i int,@j int
set @str='a,b,c,d,e'
declare @t table (fstr varchar(20))
set @i=1
set @j=charindex(',',@str,@i)
while @j>0
begin
insert into @t values (substring(@str,@i,@j-@i))
set @i=@j+1
set @j=charindex(',',@str,@i)
end
insert into @t values (substring(@str,@i,len(@str)-@i+1))
select * from @t
declare @Str nvarchar(4000)
set @Str = 'a,b,c,d,e,f,g'
declare @SQL nvarchar(4000)
set @SQL = 'select '''
set @SQL = @SQL + replace(@Str,',',''' union all select ''');
set @SQL = @SQL + ''';'
--print @SQL;
exec(@SQL);
/*--pring @SQL结果
select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' union all select 'f' union all select 'g';*/
declare @var xml
declare @var1 varchar(100)
set @var1='a,b,c,d,e'
set @var=convert(xml,replace('<root><row>'+@var1+'</row></root>',',','</row><row>'))
select Col=C.v.value('.','nvarchar(100)')
from @var.nodes('/root/row')C(v)
Col
----------------------
a
b
c
d
e
(5 行受影响)
declare @sql varchar(100),@s varchar(1000)
select @sql='a,b,c,d,e',@s=''
set @s=@s+' select '+replace(''''+@sql+'''',',',''' union all select ''')
exec( @s)
/*
----
a
b
c
d
e
(5 個資料列受到影響)*/
declare @s varchar(100),@sql varchar(8000)
set @s='a,b,c,d,e'
set @sql='select '''+replace(@s,',',''' union all select ''')+''''
exec (@sql)
/**
----
a
b
c
d
e
**/
/*
-----------------------------------
-------T-MAC ---------------------
---------------小编---------------
---------------love 轩--------
-----------------------------------
*/
拆分表:
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go
--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number)
from
Tab a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:
select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:--此法roy博客
;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/