SQL Server string split (split) method rollup

--Method 0: Dynamic SQL method
DECLARE @s varchar (+), @sql varchar (1000)
Set @s= ' 1,2,3,4,5,6,7,8,9,10 '
Set @sql = ' Select col= ' + replace (@s, ', ', ' union ALL SELECT ') + '
PRINT @sql
EXEC (@sql)

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ F_SPLITSTR] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [F_splitstr]
GO
--Method 1: Cyclic interception method
CREATE FUNCTION F_splitstr (
@s varchar (8000),--string to be split
@split varchar (10)--Data delimiter
) RETURNS @re TABLE (col varchar (100))
As
BEGIN
DECLARE @splitlen int
SET @splitlen =len (@split + ' a ')-2
While CHARINDEX (@split, @s) >0
BEGIN
INSERT @re VALUES (left (@s,charindex (@split, @s)-1))
SET @s=stuff (@s,1,charindex (@split, @s) [email protected], ')
END
INSERT @re VALUES (@s)
RETURN
END
GO

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ F_SPLITSTR] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [F_splitstr]
GO
--Method 2: Using the Temporary partition auxiliary table method
CREATE FUNCTION F_splitstr (
@s varchar (8000),--string to be split
@split varchar (10)--Data delimiter
) RETURNS @re TABLE (col varchar (100))
As
BEGIN
--Create a secondary table for split processing (only table variables can be manipulated in user-defined functions)
DECLARE @t TABLE (ID int identity,b bit)
INSERT @t (b) SELECT TOP 8000 0 from syscolumns a,syscolumns b

INSERT @re SELECT SUBSTRING (@s,id,charindex (@split, @[email protected],id)-id)
From @t
WHERE Id<=len (@s+ ' a ')
and CHARINDEX (@split, @[email protected],id) =id
RETURN
END
GO

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ F_SPLITSTR] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))
Drop function [dbo]. [F_splitstr]
GO
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ TB_SPLITSTR] ') and OBJECTPROPERTY (Id,n ' isusertable ') =1)
drop table [dbo]. [Tb_splitstr]
GO
--Method 3: Using the Permanent partition auxiliary table method
--String splitting auxiliary table
SELECT TOP 8000 id=identity (int,1,1) into Dbo.tb_splitstr
From syscolumns A,syscolumns b
GO
--string spin-off processing function
CREATE FUNCTION F_splitstr (
@s varchar (8000),--string to be split
@split varchar (10)--Data delimiter
) RETURNS TABLE
As
RETURN (
SELECT Col=cast (SUBSTRING (@s,id,charindex (@split, @[email protected],id)-id) as varchar (100))
From Tb_splitstr
WHERE Id<=len (@s+ ' a ')
and CHARINDEX (@split, @[email protected],id) =id)
GO

--Method 4: Use SQL server2005 outer APPLY

CREATE FUNCTION [dbo]. [Ufn_splitstringtotable]
(
@str VARCHAR (MAX),
@split VARCHAR (10)
)
RETURNS TABLE
As
RETURN
(SELECT b.id
From (SELECT [value] = CONVERT (XML, ' <v> ' + REPLACE (@str, @split, ' </v><v> ')
+ ' </v> ')
) A
OUTER APPLY (SELECT id = n.v.value ('. ', ' varchar (100) ')
From A.[value].nodes ('/V ') N (v)
) B
)

Remark Description:

Method 4 must be run under SQL server2005

SQL Server string split (split) method rollup