MSSQL 拆分逗号隔开的数据 Split方法.

  

--MSSQL 拆分字符串 Split方法.


--创建辅助表
CREATE TABLE Nums(Rn int NOT NULL PRIMARY KEY);


GO

 ;WITH 
 t1 AS (SELECT 1 AS c UNION ALL SELECT 1 ) --SELECT * FROM t1;
,t2 AS (SELECT 1 AS c FROM t1 AS a,t1 AS b) --SELECT * FROM t2;
,t3 AS (SELECT 1 AS c FROM t2 AS a,t2 AS b) --SELECT * FROM t3;
,t4 AS (SELECT 1 AS c FROM t3 AS a,t3 AS b) --SELECT * FROM t4;
,t5 AS (SELECT 1 AS c FROM t4 AS a,t4 AS b) --SELECT * FROM t5;
,t6 AS (SELECT 1 AS c FROM t5 AS a,t5 AS b) --SELECT * FROM t6;
,tNums  AS(
	SELECT ROW_NUMBER() OVER(ORDER BY t6.c) AS Rn FROM t6
)
INSERT INTO Nums (Rn)  SELECT *  FROM tnums t WHERE t.rn<=100000;

GO
 

-- ================================================  
 --作者:  feiazi  
 --创建日期:  -
 --修改日期：  
 --版本号：-  
 --描述:    
 --所属数据库:-  
 -- ================================================  
Create FUNCTION [dbo].[Split]  
(   
 @List nvarchar(MAX),  
 @SplitOn nvarchar(1)  
)  
RETURNS @RtnValue table (  
 Id int identity(1,1),  
 Value nvarchar(100)  
)  
AS  
BEGIN  
 INSERT INTO @RtnValue	
 SELECT SUBSTRING(@List, n.Rn,
       CHARINDEX(@SplitOn, @List + @SplitOn, n.Rn) - n.Rn) AS [Value]
    FROM    dbo.Nums n
    WHERE   n.Rn <= LEN(@List)
           AND SUBSTRING(@SplitOn + @List, n.Rn, 1) =@SplitOn;
           
    Return  
END  
  
GO


/*
 * 
 这个split方法有点慢.
CREATE FUNCTION [dbo].[Split2]    
(     
 @List nvarchar(MAX),    
 @SplitOn nvarchar(1)    
)    
RETURNS @RtnValue table (    
 Id int identity(1,1),    
 Value nvarchar(100)    
)    
AS    
BEGIN    
 While (Charindex(@SplitOn,@List)>0)    
 Begin     
  Insert Into @RtnValue (value)    
  Select     
   Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))     
  Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))    
 End     
     
 Insert Into @RtnValue (Value)    
    Select Value = ltrim(rtrim(@List))    
    
    Return    
END    

GO
*/
   
   
-----------------------
--drop table tab;
CREATE TABLE tab(id INT IDENTITY,val NVARCHAR(20));
 
INSERT INTO [dbo].[tab] ([val]) VALUES ('1')
INSERT INTO [dbo].[tab] ([val]) VALUES ('1,3')
INSERT INTO [dbo].[tab] ([val]) VALUES ('2,3,4,5')
INSERT INTO [dbo].[tab] ([val]) VALUES ('')
INSERT INTO [dbo].[tab] ([val]) VALUES (NULL)


--方法1

SELECT * FROM tab t
OUTER APPLY (
	SELECT * FROM dbo.[Split](t.val,',') s	
) r1

/*
--result

id          val                  Id          Value
----------- -------------------- ----------- ---------
1           1                    1           1
2           1,3                  1           1
2           1,3                  2           3
3           2,3,4,5              1           2
3           2,3,4,5              2           3
3           2,3,4,5              3           4
3           2,3,4,5              4           5
4                                NULL        NULL
5           NULL                 NULL        NULL

(9 行受影响)

*/
--方法2 .(效率比 方法1 快)


SELECT * FROM tab t
OUTER APPLY (
	SELECT 
	n.Rn - LEN(REPLACE(LEFT(t.val, n.Rn), ',', ''))+1 AS Id,
	SUBSTRING(t.val, n.Rn, CHARINDEX(',', t.val + ',', n.Rn) - n.Rn) AS [Value]
    FROM    dbo.Nums n
    WHERE   n.Rn <= LEN(t.val)
           AND SUBSTRING(',' + t.val, n.Rn, 1) =','	
) r1

--结果 同方法一
--


--方法3 
-- 使用自定 CLR. 使用C#的 string.Split()方法。  此CLR同 方法2的效率差不多一样的。

--第一次发，请大家多多支持.~