SQL 中超简单的模拟split

bettyly

于 2013-05-21 22:07:28 发布

阅读量551

点赞数

分类专栏： MSSQL

MSSQL 专栏收录该内容

2 篇文章

订阅专栏

 
 --> 测试数据: #T 
 
 if object_id( 
 'tempdb.dbo.#T' 
 )  
 is 
 not 
  null 
 drop 
 table 
 #T 
 
 create 
  table 
 #T (id  
 int 
 , 
 name 
 varchar 
 (8)) 
 
 insert 
  into 
 #T 
 
 select 
  1, 
 'jame,job' 
 union 
 all 
 
 select 
  2, 
 'paul,mc' 
 union 
 all 
 
 select 
  3, 
 'carl' 
 ; 
 
 --> 1. CTE 递归找分隔字符位置法：速度极快 
 
 with 
  T (id,P1,P2)  
 as 
 
 ( 
 
      
 select 
 id,charindex( 
 ',' 
 , 
 ',' 
 + 
 name 
 ),charindex( 
 ',' 
 , 
 name 
 + 
 ',' 
 )+1  
 from 
 #T 
 
      
 union 
 all 
 
      
 select 
 a.id,b.P2,charindex( 
 ',' 
 , 
 name 
 + 
 ',' 
 ,b.P2)+1  
 from 
 #T a  
 join 
 T b  
 on 
 a.id=b.id  
 where 
  charindex( 
 ',' 
 , 
 name 
 + 
 ',' 
 ,b.P2)>0 
 
 ) 
 
 select 
  a.id, 
 name 
 = 
 substring 
 (a. 
 name 
 + 
 ',' 
 ,b.P1,b.P2 - b.P1 - 1)  
 from 
  #T a  
 join 
 T b  
 on 
 a.id=b.id  
 order 
 by 
 1 