Split function in SQL

          I was working at home and extending a gridview control with some extra features I required. I have added one column with checkbox for multiple delete. To delete multiple records I need to pass either multiple ID to SP and delete all or can pass each ID one by one to SP.

        I passed all ID as coma separated string to SP.  I used split function to split the coma separated ID, so that I can use those ID in inner query. For example

DELETE FROM TableName WHERE ID IN (SELECT dbo.fun_Split(@AllID, ',' ))

Here you have to pass string value and separator as a parameter. In my case, I have used "," as seperator. Below is the function,

 

Create function fun_Split (@String nvarchar(4000), @Delimiter char(1))
Returns @Results Table (Items nvarchar(4000))
As

Begin
         Declare @Index int
         Declare @Slice nvarchar(4000)
         Select @Index = 1
        If @String Is NULL
                 Return
        While @Index != 0
        Begin
               Select @Index = CharIndex(@Delimiter, @String)
               If (@Index != 0)
                      Select @Slice = left(@String, @Index - 1)
               else
                     Select @Slice = @String

               Insert into @Results(Items) Values (@Slice)
               Select @String = right(@String, Len(@String) - @Index)
               If Len(@String) = 0
                     break
       End
       Return
End

Fig -(1) Split function SQL.

             There is another way to do the same. You can use open XML format to pass more than one parameters to SP. I will explain that in my next blog.

Happy Programing.