Write the SQL function you need

How to write a splitstringfunction in SQL, say not much, on the code:

1 SETAnsi_nulls on2 GO3 SETQuoted_identifier on4 GO5 6 CREATE function [dbo].[splitstring]7 (8     @Input nvarchar(Max),--the string to be split
@Separator nvarchar(Max)=',',---delimiter, which can be a character or multiple characters9     @RemoveEmptyEntries bit=1)--whether to remove the empty stringTen returns @TABLE Table--Returns a table that holds the separated string One ( A     [Id] int Identity(1,1), -     [Value] nvarchar(Max) - )  the  as - begin  -     Declare @Index int,@Entry nvarchar(Max) -     Set @Index = charindex(@Separator,@Input) +  -      while(@Index>0) +     begin A         Set @Entry=LTrim(RTrim(substring(@Input,1,@Index-1))) at          -         if(@RemoveEmptyEntries=0)or(@RemoveEmptyEntries=1  and @Entry<>"') -             begin -                 Insert  into @TABLE([Value])Values(@Entry) -             End -  in         Set @Input = substring(@Input,@Index+datalength(@Separator)/2,Len(@Input)) -         Set @Index = charindex(@Separator,@Input) to     End +      -     Set @Entry=LTrim(RTrim(@Input)) the     if(@RemoveEmptyEntries=0)or(@RemoveEmptyEntries=1  and @Entry<>"') *         begin $             Insert  into @TABLE([Value])Values(@Entry)Panax Notoginseng         End -  the     return + End

Both function and table are ready, next test:

Declare @str1 varchar(Max),@str2 varchar(Max),@str3 varchar(Max)Set @str1 = ' the'Set @str2 = '1## #2 # # #3'Set @str3 = '1## #2 # # #3 # # #'Select [Value]  from [dbo].[splitstring](@str1,',',1)Select [Value]  from [dbo].[splitstring](@str2,'###',1)Select [Value]  from [dbo].[splitstring](@str3,'###',0)

Results:

There is also a self-growing [id] field, which may be used in some cases, such as saving sorting by Id, and so on.

Write the SQL function you need