SQL 将一个字段内用逗号分隔的内容分成多条记录

[namea] [varchar](100) NULL, [desca] [varchar](50) NULL) ON [PRIMARY]insert into testtb values(‘A1,A2,‘,‘AAAAA‘)insert into testtb values(‘A3,‘,‘BBBBBB‘)insert into testtb values(‘A5,A8,A9,‘,‘AAACCAA‘)insert into testtb values(‘A6,A20,‘,‘AAANNNNAA‘)--select * from testtb --truncate table testtb--定义一个临时表WITH testtb2 AS (SELECT namea,desca,CHARINDEX(‘,‘,namea) STA,CHARINDEX(‘,‘,namea)-1 LENS FROM testtb  UNION ALLSELECT namea,desca,CHARINDEX(‘,‘,namea,STA+1) STA,CHARINDEX(‘,‘,namea,STA+1)-STA-1 LENS FROM testtb2 WHERE STA<>0)--SELECT * FROM testtb2  SELECT SUBSTRING(namea,STA-LENS,LENS) as [name],desca FROM testtb2WHERE STA<>0order by  SUBSTRING(namea,STA-LENS,LENS)

 

SQL 将一个字段内用逗号分隔的内容分成多条记录

标签：