Implementing the Cut string splitstring function in SQL Server

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. There's nothing to say, just take it with the friends you need.

SETAnsi_nulls onGOSETQuoted_identifier onGO/*by Kudychen 2011-9-28*/CREATE function [dbo].[splitstring](    @Input nvarchar(Max),--input string to is separated    @Separator nvarchar(Max)=',',--A string that delimit the substrings in the input string    @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 @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 = ' 1## ' Set @str2 = ' #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)

　　

Execution Result:

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

Implementing the Cut string splitstring function in SQL Server