注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

每日聊的博客

 
 
 

日志

 
 
 
 

-MSSQL 拆分字符串 Split方法.  

2014-10-28 17:04:09|  分类: SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
  1. --MSSQL 拆分字符串 Split方法. 


create TABLE tx1(列1 varchar(20));
create TABLE tx2(列1 int,列2 varchar(20));

insert into tx1 values(2);
insert into tx1 values('2,3');
insert into tx1 values(3);

insert into tx2 values(1,'好');
insert into tx2 values(2,'有用');
insert into tx2 values(3,'漂亮');



select *
from tx1 t
CROSS APPLY(
select STUFF((
SELECT ','+lt.列2 FROM tx2 lt
WHERE lt.列1 in(SELECT s.[Value] FROM dbo.Split(t.列1,',') s)
FOR XML PATH('')
),1,1,'') r
)r1;





  1. --创建辅助表  
  2. CREATE TABLE Nums(Rn int NOT NULL PRIMARY KEY);  
  3.   
  4.   
  5. GO  
  6.   
  7.  ;WITH   
  8.  t1 AS (SELECT 1 AS c UNION ALL SELECT 1 ) --SELECT * FROM t1;  
  9. ,t2 AS (SELECT 1 AS c FROM t1 AS a,t1 AS b) --SELECT * FROM t2;  
  10. ,t3 AS (SELECT 1 AS c FROM t2 AS a,t2 AS b) --SELECT * FROM t3;  
  11. ,t4 AS (SELECT 1 AS c FROM t3 AS a,t3 AS b) --SELECT * FROM t4;  
  12. ,t5 AS (SELECT 1 AS c FROM t4 AS a,t4 AS b) --SELECT * FROM t5;  
  13. ,t6 AS (SELECT 1 AS c FROM t5 AS a,t5 AS b) --SELECT * FROM t6;  
  14. ,tNums  AS(  
  15.     SELECT ROW_NUMBER() OVER(ORDER BY t6.c) AS Rn FROM t6  
  16. )  
  17. INSERT INTO Nums (Rn)  SELECT *  FROM tnums t WHERE t.rn<=100000;  
  18.   
  19. GO  
  20.    
  21.   
  22. -- ================================================    
  23.  --作者:  feiazi    
  24.  --创建日期:  -  
  25.  --修改日期:    
  26.  --版本号:-    
  27.  --描述:      
  28.  --所属数据库:-    
  29.  -- ================================================    
  30. Create FUNCTION [dbo].[Split]    
  31. (     
  32.  @List nvarchar(MAX),    
  33.  @SplitOn nvarchar(1)    
  34. )    
  35. RETURNS @RtnValue table (    
  36.  Id int identity(1,1),    
  37.  Value nvarchar(100)    
  38. )    
  39. AS    
  40. BEGIN    
  41.  INSERT INTO @RtnValue    
  42.  SELECT SUBSTRING(@List, n.Rn,  
  43.        CHARINDEX(@SplitOn, @List + @SplitOn, n.Rn) - n.Rn) AS [Value]  
  44.     FROM    dbo.Nums n  
  45.     WHERE   n.Rn <= LEN(@List)  
  46.            AND SUBSTRING(@SplitOn + @List, n.Rn, 1) =@SplitOn;  
  47.              
  48.     Return    
  49. END    
  50.     
  51. GO  
  52.   
  53.   
  54. /*  
  55.  *   
  56.  这个split方法有点慢.  
  57. CREATE FUNCTION [dbo].[Split2]      
  58. (       
  59.  @List nvarchar(MAX),      
  60.  @SplitOn nvarchar(1)      
  61. )      
  62. RETURNS @RtnValue table (      
  63.  Id int identity(1,1),      
  64.  Value nvarchar(100)      
  65. )      
  66. AS      
  67. BEGIN      
  68.  While (Charindex(@SplitOn,@List)>0)      
  69.  Begin       
  70.   Insert Into @RtnValue (value)      
  71.   Select       
  72.    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))       
  73.   Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))      
  74.  End       
  75.        
  76.  Insert Into @RtnValue (Value)      
  77.     Select Value = ltrim(rtrim(@List))      
  78.       
  79.     Return      
  80. END      
  81.   
  82. GO  
  83. */  
  84.      
  85.      
  86. -----------------------  
  87. --drop table tab;  
  88. CREATE TABLE tab(id INT IDENTITY,val NVARCHAR(20));  
  89.    
  90. INSERT INTO [dbo].[tab] ([val]) VALUES ('1')  
  91. INSERT INTO [dbo].[tab] ([val]) VALUES ('1,3')  
  92. INSERT INTO [dbo].[tab] ([val]) VALUES ('2,3,4,5')  
  93. INSERT INTO [dbo].[tab] ([val]) VALUES ('')  
  94. INSERT INTO [dbo].[tab] ([val]) VALUES (NULL)  
  95.   
  96.   
  97. --方法1  
  98.   
  99. SELECT * FROM tab t  
  100. OUTER APPLY (  
  101.     SELECT * FROM dbo.[Split](t.val,',') s    
  102. ) r1  
  103.   
  104. /*  
  105. --result  
  106.   
  107. id          val                  Id          Value  
  108. ----------- -------------------- ----------- ---------  
  109. 1           1                    1           1  
  110. 2           1,3                  1           1  
  111. 2           1,3                  2           3  
  112. 3           2,3,4,5              1           2  
  113. 3           2,3,4,5              2           3  
  114. 3           2,3,4,5              3           4  
  115. 3           2,3,4,5              4           5  
  116. 4                                NULL        NULL  
  117. 5           NULL                 NULL        NULL  
  118.   
  119. (9 行受影响)  
  120.   
  121. */  
  122. --方法2 .(效率比 方法1 快)  
  123.   
  124.   
  125. SELECT * FROM tab t  
  126. OUTER APPLY (  
  127.     SELECT   
  128.     n.Rn - LEN(REPLACE(LEFT(t.val, n.Rn), ','''))+1 AS Id,  
  129.     SUBSTRING(t.val, n.Rn, CHARINDEX(',', t.val + ',', n.Rn) - n.Rn) AS [Value]  
  130.     FROM    dbo.Nums n  
  131.     WHERE   n.Rn <= LEN(t.val)  
  132.            AND SUBSTRING(',' + t.val, n.Rn, 1) =','   
  133. ) r1  
  134.   
  135. --结果 同方法一  
  136. --  
  137.   
  138.   
  139. --方法3   
  140. -- 使用自定 CLR. 使用C#的 string.Split()方法。  此CLR同 方法2的效率差不多一样的。 
  评论这张
 
阅读(3)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017