Copy Code code 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 is separated
@Separator nvarchar (max) = ', ',--a string that delimit the substrings in the input string
@RemoveEmptyEntries bit=1--the return value does the not include array elements that contain a 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 Code code 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 results:
There's also an additional [Id] field in it. Oh, in some cases it might be used, such as saving the sort by Id, and so on.
For example, to save a sort based on the ID of a table:
Copy Code code as follows:
Update a set a.[order]=t.[id] from [dbo]. [Table] as a join [dbo]. Splitstring (' 1,2,3 ', ', ', ', 1) as T on A.[id]=t.[value]
Specific application please according to their own situation to come:
Author: kudy
