Summary of SQL Server string splitting (split) methods

--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 methodifExists (Select* fromDbo.sysobjectswhereid = object_id (N'[dbo]. [F_splitstr]') and Xtypeinch(N'FN'N'IF'N'TF') drop function [dbo]. [F_splitstr] Gocreate FUNCTION f_splitstr (@s varchar (8000),   --string to be split @split varchar (Ten)     --data delimiter) RETURNS @re TABLE (col varchar ( -)) Asbegin DECLARE @splitlenintSET @splitlen=len (@split +'a')-2while CHARINDEX (@split, @s)>0BEGIN INSERT @re VALUES (left (@s,charindex (@split, @s)-1)) SET @s=stuff (@s,1, CHARINDEX (@split, @s) [email protected],"') END INSERT @re VALUES (@s) Returnendgo--Method 2: Use the Temporary split auxiliary table methodifExists (Select* fromDbo.sysobjectswhereid = object_id (N'[dbo]. [F_splitstr]') and Xtypeinch(N'FN'N'IF'N'TF') drop function [dbo]. [F_splitstr] Gocreate FUNCTION f_splitstr (@s varchar (8000),  --string to be split @split varchar (Ten)     --data delimiter) RETURNS @re TABLE (col varchar ( -)) Asbegin--Create a secondary table for split processing (only table variables can be manipulated in a user-defined function) DECLARE @t table (IDintidentity,b bit) INSERT @t (b) SELECT TOP8000 0From syscolumns a,syscolumns b INSERT @re SELECT SUBSTRING (@s,id,charindex (@split, @s[Email Protected],id]-ID) from @t WHERE ID<=len (@s+'a') and CHARINDEX (@split, @split[email Protected],id] =ID Returnendgo--Method 3: Use the Permanent split auxiliary table methodifExists (Select* fromDbo.sysobjectswhereid = object_id (N'[dbo]. [F_splitstr]') and Xtypeinch(N'FN'N'IF'N'TF') drop function [dbo]. [F_splitstr] GOifExists (Select* fromDbo.sysobjectswhereid = object_id (N'[dbo]. [Tb_splitstr]') and OBJECTPROPERTY (id,n'isusertable')=1) drop table [dbo]. [Tb_splitstr] GO--String Split Auxiliary table select TOP8000Id=identity (int,1,1) into Dbo.tb_splitstrfrom syscolumns a,syscolumns BGO--string spin-off handler for Create function f_splitstr (@s varchar (8000),  --string to be split @split varchar (Ten)     --data delimiter) RETURNS Tableasreturn (SELECT col=cast (SUBSTRING (@s,id,charindex (@split, @[email protected],id)-id) asvarchar -) ) from Tb_splitstr WHERE ID<=len (@s+'a') and CHARINDEX (@split, @split[email Protected],id] =ID) GO--Method 4: Loop string splits the Create FUNCTION [dbo]. [Fun_splitstr] (@originalStr VARCHAR (8000), --the string to split @split varchar ( -)  --delimited symbols) RETURNS @temp TABLE (Result VARCHAR ( -)) Asbegin DECLARE @result as VARCHAR ( -); --defines a variable to receive a single result SET @originalStr= @originalStr +@split; while (@originalStr<>"') BEGIN SET @result= Left (@originalStr, CHARINDEX (@split, @originalStr,1) -1) ;                    INSERT @temp VALUES (@result); --The STUFF () function deletes characters of a specified length and can insert another set of characters at the specified starting point. SET @originalStr= STUFF (@originalStr,1, CHARINDEX (@split, @originalStr,1),"'); END Returnend--Method 5: Take advantage of the SQL server2005 outer applycreate FUNCTION [dbo]. [Ufn_splitstringtotable] (@str varchar (MAX), @split varchar (Ten) ) 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 (+)') from A.[value].nodes ('/ v') N (v)) B)

Summary of SQL Server string splitting (split) methods