字符串整理系列

原帖地址:http://topic.csdn.net/u/20090209/08/a945701c-e0d5-40cb-85f2-f4f56ac2999b.html

--(1)字符串分拆并统计 
create table #tb   
(   
 id int,   
 col varchar(50),   
 num int  
)   
insert into #tb select 1,'aa,bb,cc',10   
union all select 2,'aa,aa,bb',20   
union all select 3,'aa,aa,bb',20   
union all select 4,'dd,ccc,c',30   
union all select 5,'ddaa,ccc',40   
union all select 6,'eee,ee,c',50   
  
declare @Len int  
select top 1 @Len=len(col)+1 from #tb order by len(col)   
select @Len   
set rowcount @Len   
select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B   
set rowcount 0   
;with hgo as  
(   
   select b.id,   
          number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)   
    from #TT A join #tb b on substring(','+col,A.id,1)=','  
)   
select number,count(distinct id) [count],count(number) [number] from hgo group by number 
--(2)分拆字符串求和  
create table #tb   
(   
 id int,   
 col varchar(50),   
 num int  
)   
insert into #tb select 1,'aa,bb,cc',10   
union all select 2,'aa,aa,bb',20   
union all select 3,'aa,aa,bb',20   
union all select 4,'dd,ccc,c',30   
union all select 5,'ddaa,ccc',40   
union all select 6,'eee,ee,c',50   
  
declare @len int  
select top 1 @len=len(col)+1 from #tb order by len(col) desc   
  
set rowcount @len   
select id=identity(int,1,1) into # from dbo.syscolumns A,dbo.syscolumns B   
set rowcount 0   
  
;with hgo as  
(   
   select distinct data=substring(A.col,b.id,charindex(',',A.col+',',b.id)-b.id),   
   A.num,b.id   
   from #tb A,# b    
   where substring(','+A.col,b.id,1)=','  
)    
select data,sum(num) from hgo group by data  
--(3)合并法(函数)
create table tb1 
( 
  col1 varchar(10), 
  col2 int 
) 
insert into tb1 select 'a',1 
union all select 'a',2 
union all select 'b',1 
union all select 'b',2 
union all select 'b',3 
create function dbo.FC_Str(@col1 varchar(100)) 
returns varchar(100) 
as 
begin 
   declare @i varchar(100) 
   set @i='' 
   select @i=@i+','+cast(col2 as varchar) from tb1 where col1=@col1 
   return(stuff(@i,1,1,'')) 

end 
select col1,dbo.FC_Str(col1) from tb1 group by col1 
--(4)固定行合并法 
create table #tb 
( 
  col1 varchar(10), 
  col2 int 
) 
insert into #tb select 'a',1 
union all select 'a',2 
union all select 'b',1 
union all select 'b',2 
union all select 'c',3 

select col1, 
       col2=cast(min(col2) as varchar)+ 
       case when count(*)=1 then '' 
       else +','+cast(max(col2) as varchar) end from #tb 
group by col1 
--(5)临时表合并法 
if object_id('tb') is not null drop table tb 
create table tb 
( 
  col1 varchar(10), 
  col2 int 
) 
insert into tb select 'a',1 
union all select 'a',2 
union all select 'b',1 
union all select 'b',2 
union all select 'b',3 

select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2 
declare @col1 varchar(20) 
declare @col2 varchar(100) 
update #t1 set @col2= 
                   case when @col1=col1 then @col2+ ',' +col2 
                   else col2 end, 
           @col1=col1, 
           col2=@col2 

select * from #t1 

select col1,col2=max(col2) from #t1 group by col1
--(6)字符串拆分 
create function FC_SlpitStr(@Str nvarchar(4000),@Split nvarchar(100))
returns @R table (Col nvarchar(100))
as
begin
    declare @StrLen int
    set @StrLen=len(@Str)
    while charindex(@Split,@StrLen)>0
     begin
      insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
       set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
      end
     insert into @R values(@Str)
return 
end 

declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'

declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)

--(8)分拆数据到列
;declare @tb table
(
  col varchar(20)
)
insert into @tb select 'aa,bb,cc'
union all select 'AAA,BB'
union all select 'AAA'

declare @i int,@j varchar
set @j=1
select col+',' as col into #t1 from @tb--加上','
select @i=max(len(Col)-len(replace(Col,',',''))) from #t1

while @j!>@i
begin
    exec('alter table #t1 add  [Col'+@j+'] varchar(10)')
    exec('update #t1 set [Col'+@j+']=left(col,charindex('','',col)-1),col=stuff(col,1,charindex('','',col),'''') where Len(col)>1')
    set @j=@j+1
end
alter table #T1 drop column Col
select * from #t1
drop table #t1;
/*
Col1       Col2       Col3
---------- ---------- ----------
aa         bb         cc
AAA        BB         NULL
AAA        NULL       NULL

*/
--(9)字符串分拆并统计
create table #tb   
(   
 id int,   
 col varchar(50),   
 num int  
)   
insert into #tb select 1,'aa,bb,cc',10   
union all select 2,'aa,aa,bb',20   
union all select 3,'aa,aa,bb',20   
union all select 4,'dd,ccc,c',30   
union all select 5,'ddaa,ccc',40   
union all select 6,'eee,ee,c',50   
  
declare @Len int  
select top 1 @Len=len(col)+1 from #tb order by len(col)   
select @Len   
set rowcount @Len   
select ID=identity(int,1,1) into #TT from dbo.syscolumns A,dbo.syscolumns B   
set rowcount 0   
;with hgo as  
(   
   select b.id,   
          number=substring(col,A.id,charindex(',',col+',',A.id)-A.id)   
    from #TT A join #tb b on substring(','+col,A.id,1)=','  
)   
select number,count(distinct id) [count],count(number) [number] from hgo group by number
number                                             count       number
-------------------------------------------------- ----------- -----------
aa                                                 3           5
bb                                                 3           3
c                                                  2           2
cc                                                 1           1
ccc                                                2           2
dd                                                 1           1
ddaa                                               1           1
ee                                                 1           1
eee                                                1           1

(9 行受影响)