Split () functionality implemented in SQL

Original: Split () functionality implemented in SQL

Http://www.cnblogs.com/yangyy753/archive/2011/11/23/2260618.html

Database, always encounter some field content, want to intercept a string according to a certain identity, but do not think of a good way, if you can have a split method like ASP, then how good, how convenient ah!

Why can't you? Can't we just write a function of our own to solve it? All right, let's do it, write a splitstring function yourself!

SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE function [dbo].[splitstring](    @Input nvarchar(Max),@Separator nvarchar(Max)=',',     @RemoveEmptyEntries bit=1 )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

The functions and tables have been built, so let's call the test below:

Declare @str1 varchar(Max),@str2 varchar(Max),@str3 varchar(Max)Set @str1 = ' the'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)

As a result, take a look at the figure:

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:

 UpdateASetA.[Order]=T.[Id]         from [dbo].[Table]  asAJoin [dbo]. Splitstring (' the',',',1) asT onA.[Id]=T.[Value]

Split () functionality implemented in SQL