--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