Use custom Split () in SQL to meet Personalized Requirements

Copy codeThe Code is as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo]. [SplitString]
(
@ Input nvarchar (max ),
@ Separator nvarchar (max) = ',',
@ RemoveEmptyEntries bit = 1
)
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

The functions and tables have been created. Let's call and test them:
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)

The result is as follows: