求一SQL语句,还是字符串截取的问题,望高手们指点,在线等。
--建表:CREATE TABLE [dbo].[tb]( [AB] [nvarchar](2000) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]--插入数据insert into tb(AB)values('张三,李四,王五')insert into tb(AB)values('张三,李四,王五,赵柳,王八')insert into tb(AB)values('张无忌,张三丰,小名,赵刚,黎明,小凡,赵本山,范围')insert into tb(AB)values('小名,赵刚,黎明,小凡,小李')insert into tb(AB)values('ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD')
张三,李四,王五张三,李四,王五,<|>赵柳,王八张无忌,张三丰,小名,<|>赵刚,黎明,小凡,<|>赵本山,范围小名,赵刚,黎明,<|>小凡,小李ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD
with cte as(select m.num,value=case when num1%4=0 then '<|>'+m.value else m.value end from(select row_number()over(partition by num order by getdate()) num1,* from(select a.num,value=substring(A.ab,b.number,CHARINDEX(',',a.ab+',',b.number)-b.number) from(select ROW_NUMBER()over( order by getdate()) num,AB from #tb )a join master..spt_values b on type='p' and number<=len(a.ab) where b.number=CHARINDEX(',',','+a.AB,b.number)) b)m )select replace(STUFF((select ','+value from cte where num=a.num for XML path('')),1,1,''),'<|>','<|>') from cte a group by num
[解决办法]
CREATE TABLE [dbo].[tb]( [AB] [nvarchar](2000)) ON [PRIMARY]--插入数据insert into tb(AB)values('张三,李四,王五')insert into tb(AB)values('张三,李四,王五,赵柳,王八')insert into tb(AB)values('张无忌,张三丰,小名,赵刚,黎明,小凡,赵本山,范围')insert into tb(AB)values('小名,赵刚,黎明,小凡,小李')insert into tb(AB)values('ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD')gocreate function dbo.f_str(@ab varchar(2000)) returns varchar(2000)asbegin declare @str varchar(2000) declare @i as int declare @j as int set @j = len(@ab) - len(replace(@ab,',','')) if @j < 3 set @str = @ab else begin set @i = 1 set @str = '' while @i <= @j begin set @str = @str + substring(@ab,1,charindex(',',@ab)) if @i % 3 = 0 set @str = @str + '<|>' set @ab = substring(@ab, charindex(',',@ab) + 1 , len(@ab)) set @i = @i + 1 end set @str = @str + @ab end return @strendgoselect ab ,dbo.f_str(ab) newab From tbdrop table tbdrop function dbo.f_str/*ab newab ---------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------- 张三,李四,王五 张三,李四,王五张三,李四,王五,赵柳,王八 张三,李四,王五,<|>赵柳,王八张无忌,张三丰,小名,赵刚,黎明,小凡,赵本山,范围 张无忌,张三丰,小名,<|>赵刚,黎明,小凡,<|>赵本山,范围小名,赵刚,黎明,小凡,小李 小名,赵刚,黎明,<|>小凡,小李ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD,ABD ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD(所影响的行数为 5 行)*/
[解决办法]
有点儿小问题,改下
with cte as(select m.num,value=case when (num1-1)%3=0 then '<|>'+m.value else m.value end from(select row_number()over(partition by num order by getdate()) num1,* from(select a.num,value=substring(A.ab,b.number,CHARINDEX(',',a.ab+',',b.number)+1-b.number) from(select ROW_NUMBER()over( order by getdate()) num,AB from #tb )a join master..spt_values b on type='p' and number<=len(a.ab) where b.number=CHARINDEX(',',','+a.AB,b.number)) b)m )select stuff(replace((select ''+value from cte where num=a.num for XML path('')),'<|>','<|>'),1,3,'') from cte a group by num