--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的效率差不多一样的。
--第一次发,请大家多多支持.~