Split string SplitString function in SQLServer

Copy codeThe Code is as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
By kudychen 2011-9-28
*/
CREATE function [dbo]. [SplitString]
(
@ Input nvarchar (max), -- input string to be separated
@ Separator nvarchar (max) = ',', -- a string that delimit the substrings in the input string
@ RemoveEmptyEntries bit = 1 -- the return value does not include array elements that contain an empty string
)
Returns @ TABLE table
(
[Id] int identity (1, 1 ),
[Value] nvarchar (max)
)
As
Begin
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
Return
End

How to use:
Copy codeThe Code is as follows:
Declare @ str1 varchar (max), @ str2 varchar (max), @ str3 varchar (max)
Set @ str1 = '1, 2, 3'
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)

Execution result:

There is also an auto-increment [Id] field, which may be used in some cases, such as saving and sorting by Id.

For example, sort by table ID:
Copy codeThe Code is as follows: update a set. [Order] = t. [Id] from [dbo]. [Table] as a join [dbo]. splitString ('1, 2, 3 ', 1) as t on. [Id] = t. [Value]

For specific applications, please follow your own situation :)
Author: Kudy