String segmentation functions in SQL

The code is as follows:

Copy code

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

Test Code:

The code is as follows:	Copy code
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)

Code:

The code is as follows:

Copy code

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

Test Code:

The code is as follows:	Copy code
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)