if object_id('[TB_Test]') is not null drop table [TB_Test]
go
create table [TB_Test]([id] int,[value] varchar(11))
insert [TB_Test]
select 1,'A,B,C^E,F^G' union all
select 2,'H,I^J,K' union all
select 3,'M^N^O,P,Q'
go
--select * from [TB_Test]select id=identity(int,1,1),value=substring(a.val,b.number,charindex(',',a.val+',',b.number)-b.number)
into #
from (select replace(value,'^',',') val from TB_Test) a
join (select number from master..spt_values where type='P') b
on substring(','+a.val,b.number,1)=','select * from #
/*
id value
----------- ----------
1 A
2 B
3 C
4 E
5 F
6 G
7 H
8 I
9 J
10 K
11 M
12 N
13 O
14 P
15 Q(15 行受影响)
*/
drop table #
go
create table [TB_Test]([id] int,[value] varchar(11))
insert [TB_Test]
select 1,'A,B,C^E,F^G' union all
select 2,'H,I^J,K' union all
select 3,'M^N^O,P,Q'
go
--select * from [TB_Test]select id=identity(int,1,1),value=substring(a.val,b.number,charindex(',',a.val+',',b.number)-b.number)
into #
from (select replace(value,'^',',') val from TB_Test) a
join (select number from master..spt_values where type='P') b
on substring(','+a.val,b.number,1)=','select * from #
/*
id value
----------- ----------
1 A
2 B
3 C
4 E
5 F
6 G
7 H
8 I
9 J
10 K
11 M
12 N
13 O
14 P
15 Q(15 行受影响)
*/
drop table #
Insert Tab
select 1,N'a,b^c' union all
select 2,N'd,e' union all
select 3,N'f^g^h'
Go
update tab set col2=replace(col2,'^',',')
;with cte 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 cte where split>''
)
select COl1,COl2 from cte order by COl1 option (MAXRECURSION 0)
go
drop table tab
Insert Tab
select 1,N'a,b^c' union all
select 2,N'd,e' union all
select 3,N'f^g^h'
Go
;with cte as
(select Col1,COl2=cast(left(replace(col2,'^',','),charindex(',',replace(col2,'^',',')+',')-1) as nvarchar(100)),Split=cast(stuff(replace(col2,'^',',')+',',1,charindex(',',replace(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 cte where split>''
)
select COl1,COl2 from cte order by COl1 option (MAXRECURSION 0)
go
drop table tab
如:
ID Col1 Col2
1 A,B,C^E,F^G A,B,C^D
2 H^I,J,K E^F,G
3 M,N H,I,J,K^M,N還可以這么辦嗎?