if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[SplitTextToString]'') and xtype in (N''FN'', N''IF'', N''TF''))
drop function [dbo].[SplitTextToString]
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


Create Function dbo.SplitTextToString(@Str text)


Returns @StrTable Table (ID Varchar(50))


/*
Select * From dbo.SplitTextToString(''10,11,13'')
Select * From dbo.SplitTextToString(''aa,bb,cc'')
Select * From dbo.SplitTextToString(''10,11,1'')
Select * From dbo.SplitTextToString('',11,'')
Select * From dbo.SplitTextToString('','')
Select * From dbo.SplitTextToString(''1,'')
Select * From dbo.SplitTextToString('',1'')
Select * From dbo.SplitTextToString('''')
Select * From dbo.SplitTextToString('',,'')
*/


As


Begin
        Declare @StrID Varchar(32), @tStrID Varchar(8000);
        Declare @SplitLoc Int, @PrevSplitLoc Int, @ComaLoc Int, @PrevComaLoc Int;
        Set @PrevSplitLoc = 1;
        Set @SplitLoc = 8000; 
        Set @PrevComaLoc = 0;
        While (1=1)
        Begin
                If ((Substring(@Str, @SplitLoc, 1) = '','') OR (Substring(@Str, @SplitLoc, 1) = ''''))
                Begin
                        Set @tStrID = Substring(@Str, @PrevSplitLoc, @SplitLoc);
                        Set @ComaLoc = Charindex('','', @tStrID);
                        Break;
                End
                Else
                Begin
                        Set @SplitLoc = @SplitLoc - 1;
                End
        End
        While (@ComaLoc > 0)
        Begin
                While (@ComaLoc > 0)
                Begin
                        Select @StrID = Substring(@tStrID, @PrevComaLoc + 1, Case @ComaLoc When 0 Then len(@tStrID) Else @ComaLoc-1-@PrevComaLoc End)
                        If Len(@StrID) > 0 Insert Into @StrTable Values (@StrID)
                        Set @PrevComaLoc = @ComaLoc
                        Set @ComaLoc = Charindex('','', @tStrID, @ComaLoc + 1)
                End
                Set @PrevSplitLoc = @SplitLoc;
                Set @SplitLoc = @SplitLoc + 8000;
                If Datalength(@Str) > @PrevSplitLoc
                While (1=1)
                Begin
                        If (Substring(@Str, @SplitLoc, 1) = '','' OR Substring(@Str, @SplitLoc, 1) = '''')
                        Begin
                                Set @tStrID = Substring(@Str, @PrevSplitLoc + 1, @SplitLoc);
                                Set @ComaLoc = Charindex('','', @tStrID);
                                Set @PrevComaLoc = 0;
                                Break;
                        End
                        Else
                        Begin
                                Set @SplitLoc = @SplitLoc - 1;
                        End
                End
        End
        Select @StrID = Substring(@tStrID, @PrevComaLoc + 1, Case @ComaLoc When 0 Then len(@tStrID) Else @PrevComaLoc-1 End)
        If Len(@StrID) > 0 Insert Into @StrTable Values (@StrID)
        Return 
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO