字符串整理系列

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

view plaincopy to clipboardprint?
--(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 行受影响) 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/01/22/5224723.aspx