SQL 将一个字段内用逗号分隔的内容分成多条记录
时间:2021-07-01 10:21:17
帮助过:3人阅读
[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 将一个字段内用逗号分隔的内容分成多条记录
标签: