首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

求一SQL语句,还是字符串截取的有关问题,望高手们指点,

2012-04-14 
求一SQL语句,还是字符串截取的问题,望高手们指点,在线等。SQL code--建表:CREATE TABLE [dbo].[tb]([AB] [n

求一SQL语句,还是字符串截取的问题,望高手们指点,在线等。

SQL code
--建表: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')

查询之后得到的格式如下:
SQL code
张三,李四,王五张三,李四,王五,<|>赵柳,王八张无忌,张三丰,小名,<|>赵刚,黎明,小凡,<|>赵本山,范围小名,赵刚,黎明,<|>小凡,小李ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD,<|>ABD,ABD,ABD

也就是查找逗号 第三个逗号的后面追加一个“<|>”的符号,注意的是要保留逗号,望高手指点指点。

[解决办法]
SQL code
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,''),'&lt;|&gt;','<|>') from cte a group by num
[解决办法]
SQL code
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 行)*/ 


[解决办法]
有点儿小问题,改下

SQL code
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('')),'&lt;|&gt;','<|>'),1,3,'') from cte a group by num 

热点排行