SQL Server string split (split) method rollup

--Method 0: Dynamic SQL method

Declare @s varchar( -),@sql varchar( +)Set @s='1,2,3,4,5,6,7,8,9,10'Set @sql='Select Col=" "+ Replace(@s,','," "UNION ALL Select" ")+" '"PRINT @sqlexec(@sql)

--Method 1: Cyclic interception method

if exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [F_splitstr]') andXtypeinch(N'FN'N'IF'N'TF'))Drop function [dbo].[F_splitstr]GOCREATE FUNCTIONF_splitstr (@s   varchar(8000),--string to be split@split varchar(Ten)--Data Separators)RETURNS @re TABLE(colvarchar( -)) asBEGIN 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)+@splitlen,"') END INSERT @re VALUES(@s) RETURNENDGO

--Method 2: Using the Temporary partition auxiliary table method

if exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [F_splitstr]') andXtypeinch(N'FN'N'IF'N'TF'))Drop function [dbo].[F_splitstr]GOCREATE FUNCTIONF_splitstr (@s   varchar(8000),--string to be split@split varchar(Ten)--Data Separators)RETURNS @re TABLE(colvarchar( -)) asBEGIN --Create a secondary table for split processing (only table variables can be manipulated in a user-defined function) DECLARE @t TABLE(IDint IDENTITYBbit) INSERT @t(b)SELECT TOP 8000 0  fromsyscolumns a,syscolumns bINSERT @re SELECT SUBSTRING(@sIdCHARINDEX(@split,@s+@split, ID)-ID) from @t WHEREId<=LEN(@s+'a')    and CHARINDEX(@split,@split+@s, ID)=IDRETURNENDGO if exists(Select *  fromDbo.sysobjectswhereId= object_id(N'[dbo]. [F_splitstr]') andXtypeinch(N'FN'N'IF'N'TF'))Drop function [dbo].[F_splitstr]GOif exists(Select *  fromDbo.sysobjectswhereId= 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 tableSELECT TOP 8000Id=IDENTITY(int,1,1) intoDbo.tb_splitstr fromsyscolumns a,syscolumns bGO--string spin-off handler functionCREATE FUNCTIONF_splitstr (@s     varchar(8000),--string to be split@split  varchar(Ten)--Data Separators)RETURNS TABLE asRETURN( SELECTCol=CAST(SUBSTRING(@sIdCHARINDEX(@split,@s+@split, ID)-ID) as varchar( -))  fromTb_splitstrWHEREId<=LEN(@s+'a')    and CHARINDEX(@split,@split+@s, ID)=ID)GO

--Method 4: Use SQL server2005 outer APPLY

CREATE FUNCTION [dbo].[ufn_splitstringtotable](  @str VARCHAR(MAX) ,  @split VARCHAR(Ten))RETURNS TABLE     as RETURN    ( SELECTb.id from(SELECT    [value] = CONVERT(XML,'<v>' + REPLACE(@str,@split,'</v><v>')                            + '</v>')) AOUTERAPPLY (SELECTId=N.v.value ('.','varchar (+)')                     fromA.[value]. Nodes ('/ v') N (v)) B)

Remark Description:

Method 4 must be run under SQL server2005

Turn: Aierong original Technical essay SQL Server string Split (split) method summary

SQL Server string split (split) method rollup