Implement the Cut string splitstring function in SQL Server and return to the table

Sometimes we use the bulk operation to split the string, but SQL Server does not have the Split function, so you have to implement it yourself.

-- =============================================--Author:chenlong--Create date:2015-02-02--Description: Split string separated by commas, return table-- =============================================ALTER FUNCTION [dbo].[fn_splitstring] (    @Input nvarchar(Max),--Input String    @Separator nvarchar(Max)=',',--Separators    @RemoveEmptyEntries bit=1 --The return value does not include the array elements this contain an empty string)RETURNS @TABLE Table(    [Id] int Identity(1,1),    [Value] nvarchar(Max))  asBEGIN    --Declare The return variable here    Declare @Index int,@Entry nvarchar(Max)    Set @Index = charindex(@Separator,@Input)     while(@Index>0)    begin        Set @Entry=LTrim(RTrim(substring(@Input,1,@Index-1)))                if(@RemoveEmptyEntries=0)or(@RemoveEmptyEntries=1  and @Entry<>"')            begin                Insert  into @TABLE([Value])Values(@Entry)            End        Set @Input = substring(@Input,@Index+datalength(@Separator)/2,Len(@Input))        Set @Index = charindex(@Separator,@Input)    End        Set @Entry=LTrim(RTrim(@Input))    if(@RemoveEmptyEntries=0)or(@RemoveEmptyEntries=1  and @Entry<>"')        begin            Insert  into @TABLE([Value])Values(@Entry)        End    returnEND

How to use:

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)

The execution results are as follows:

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

For example, save the sort based on the ID of a table:

Update a set A.[order]=t.[id]
from [dbo]. [Table] as a join [dbo]. Splitstring (' All-in-all ', ', ', 1) as T on A.[id]=t.[value]

Specific application please according to your own situation come:)

Method Two:

Create function [dbo].[F_split](@aString varchar(Max),@pattern varchar(Ten))returns @temp Table(rintAvarchar( -))--functions that implement the Split function--Select a from Dbo.f_split (' I: they a:a:b: ', ': ') asbegin    Declare @i int    Declare @row int    Set @row=1    Set @aString=RTrim(LTrim(@aString))    Set @i=charindex(@pattern,@aString)     while @i>=1    begin        Insert @temp Values(@row, Left(@aString,@i-1))        Set @aString=right(@aString,Len(@aString)-@i)        Set @i=charindex(@pattern,@aString)        Set @row=@row+1    End    if @aString<>"'       Insert @temp Values(@row,@aString)    returnEnd

Implement the Cut string splitstring function in SQL Server and return to the table