SQL里面用自定义Split()完成个性化需求

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEfunction[dbo].[SplitString]
(
@Inputnvarchar(max),
@Separatornvarchar(max)=",",
@RemoveEmptyEntriesbit=1
)
returns@TABLEtable
(
[Id]intidentity(1,1),
[Value]nvarchar(max)
)
as
begin
declare@Indexint,@Entrynvarchar(max)
set@Index=charindex(@Separator,@Input)
while(@Index>0)
begin
set@Entry=ltrim(rtrim(substring(@Input,1,@Index-1)))
if(@RemoveEmptyEntries=0)or(@RemoveEmptyEntries=1and@Entry<>"")
begin
insertinto@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=1and@Entry<>"")
begin
insertinto@TABLE([Value])Values(@Entry)
end
return
end