Comparison of several methods for the implementation of split string

In the database development, sometimes encountered the string, according to a certain rule of the segmentation, such as "a,b,c,1,2,3" such as a comma-delimited string, you need to split out, divided into a single row of records. The first thought is to use the substitution function to remove the delimiter. So there was:

IF (object_id (N ' Tempdb). # #T1 ') is not NULL) BEGIN DROP TABLE # #T1; Endgodeclare @string NVARCHAR (MAX); SET @string =n ' 123,abc,456,aaa,ddd '; SET @string =n ' select * to # #T1 from (select "+ REPLACE (@string, ', ', ' as result UNION all SELECT ') + ') a '; EXEC (@string); SELECT * from # #T1; GO

Code-1: Method 1

This method stitching SQL statements, simple and ingenious, but there are deficiencies. (1) The concatenation of SQL is not intuitive, more difficult to write, (2) If the delimiter is a half-width of the single quotation mark, you need to deal with it, (3) In some cases, if the string has Chinese and other non-English characters, it will show garbled; Then the concatenation of SQL may be too long to be executed. So this method can only be used for simple substitution or pioneering thinking.

Method 2 (Recommended):

if object_id (N ' Fn_split_rowno ')  is not nullbegin    drop function  fn_split_rowno; Endgocreate function fn_split_rowno (     @str         nvarchar (MAX)    , @split      nvarchar  =  ', ') RETURNS  @t table (Row_no int ,col nvarchar) asbegin    declare @ i int    set @i = 0    while  (CHARINDEX (@ SPLIT&NBSP, @str)  <> 0)     BEGIN         INSERT @t  (Row_no,col)         values (@i + 1, SUBSTRING (@str  ,1 ,charindex (@split  , @str)  -1)               SET  @str  = stuff (@str  ,1 ,charindex (@split &nbsp, @str)  + len (@split)  -1 , ")         SET  @i = @i + 1     end      if  (@ str <>  ')         INSERT @t  (Row_no,col)         values (@i + 1 , @str)          returnendgo

Code-2: Method 2 (recommended)

Encapsulated into functions that are convenient to call, and do not appear in the problem in Method 1.

SELECT * from Fn_split_rowno (N ' 123,abc,456,aaa,ddd,51cto ', ', ')

Code-3: Calling function

Method 3 (from the network):

declare  @string  nvarchar (MAX) set  @string  = n ' 123,abc,456,aaa,ddd, blog Park ' select  REPLACE (REVERSE (Left (S ,charindex ('  ,s)))  , ', '  , ')  AS resultFROM     (           select r,reverse (Left (@ string ,r))  +  ', '  AS s            FROM    (                       SELECT  (                                   select count (*)                                    from   sys.objects                                   WHERE  NAME <= t.name                               )  AS r                       from   sys.objects  AS t                   )  a           where  r  <= len (@string)  and left (@string  +  ', '  ,r + 1)  like   '%, '        )  torder by r 

Code-3: Method 3

Method 4 (from the network):

DECLARE @string NVARCHAR (MAX) SET @string = N ' 123,abc,456,aaa,ddd ' DECLARE @idoc INT;  DECLARE @doc XML; SET @doc = CAST (' <Root><item><S> ' + REPLACE (@string, ', ', ' </s></item><item><s > ') + ' </S></item></Root> ' as XML ' EXEC sp_xml_preparedocument @Idoc OUTPUT, @docSELECT * from OPENXML (@Idoc, '/root/item ', 2) with ([S] VARCHAR (Ten)) GO

Code-4: Method 4

The following two methods are too complex and have deficiencies.

This article is from the "fishparadise" blog, make sure to keep this source http://fishparadise.blog.51cto.com/11284420/1766935

Comparison of several methods for the implementation of split string