SQL Server custom string splitting function--split

I believe most people have come across that when processing data, the value of the field is separated by ', ' (comma), so I can't avoid it.

Then I learned that SQL is no way to split a string like C # and Javascript. (Split)

So I've defined a SQL function (a multi-declaration table-valued function), with the following code:

1  Use [Test]2 GO3 /** * * * object:userdefinedfunction [dbo].    [Split] Script DATE:2017/4/14 23:04:08 * * * * **/4 SETAnsi_nulls on5 GO6 SETQuoted_identifier on7 GO8 CREATE FUNCTION [dbo].[Split](@separator VARCHAR( -)=',',@string NVARCHAR(Max))9 RETURNS @ResultTab TABLE (TenIdINT , OneResNVARCHAR( -)  A ) -  as - BEGIN the     DECLARE @Num INT -      -     IF(@string  is  not NULL  and @string <> "'  and LEN(@string)>0) -     BEGIN +         IF(CHARINDEX(@separator,@string)>0)--determine if the character to be intercepted exists -         BEGIN +             SET @Num=0 A              while(CHARINDEX(@separator,@string)>0)--if the character you want to intercept exists, continue looping at             BEGIN -                 SET @Num=@Num+1 -                  -                 INSERT  into @ResultTab(Id,res)--intercepting a string, inserting a table variable -                 SELECT @Num, Left(@string,CHARINDEX(@separator,@string)-1) -                  in                 --Delete the string that has been intercepted and inserted -                 SET @string=STUFF(@string,1,CHARINDEX(@separator,@string)-1+LEN(@separator),"')     to             END +              -             --if the last truncated string is empty, it is not inserted. the             --For example: ' 123,456,789, ' The last thing left is an empty string. *             IF(@string  is  not NULL  and @string <> "') $             BEGINPanax Notoginseng                 INSERT  into @ResultTab(id,res) -                 SELECT @Num+1,@string  the             END             +         END A         ELSE the         BEGIN +             DELETE  from @ResultTab -         END $     END $     ELSE -     BEGIN -         DELETE  from @ResultTab the     END -     RETURNWuyi END

Let's call the test results below:

1 --Since this function is created by default to be separated by commas, it is only necessary to use the default keyword.2 Select *  fromSplit (default,'123,456,789')3 4 Select *  fromSplit (default,'123,456,789,')5 6 --Try other character segmentation below7 Select *  fromSplit ('ABC','11111abc22222abc33333')8 9 Select *  fromSplit ('ABC','11111ABC22222ABC33333ABC')

The disadvantage is that you cannot directly act on a table, and you cannot support multi-character segmentation at the same time.

Of course, if more than a few parameters, but also reluctantly, but I would like to be able and C # that can pass in a split character array, but it seems that SQL custom function parameters do not support the table type.

In addition, if there are any good ideas, you can explore. Thank you ~

SQL Server custom string splitting function--split