Reusable SQL function to split a string

All Questions ASP.NET CSharp SQL Server	All Questions WCF MVC HR Round
Search This Site

Write a reusable split function that can be used to split any given string with a given delimiter?

To further understand the question better, consider the examples below
Input String : 123|456|234|435
Delimiter Character : |
The output should be as shown below:
101
102
103
104
105



Create Function SplitFunction (@InputString nvarchar(max), @Delimiter Char(1))
Returns @ResultTable Table (Items nvarchar(4000))
as
Begin
   Declare @Index int
   Declare @Slice nvarchar(4000)
   Set @Index = 1

   If(@InputString is null)
   Begin
      Return
   End

   While (@Index != 0)
   Begin
      Set @Index = CHARINDEX(@Delimiter, @InputString)

      If(@Index != 0)
         Begin
            Select @Slice = LEFT(@InputString, @Index - 1)
         End
      Else
         Begin
            Select @Slice = @InputString
         End

      Insert into @ResultTable(Items) Values (@Slice)

      SET @InputString = RIGHT(@InputString, LEN(@InputString) - @Index)

      If(LEN(@InputString)) = 0
      Begin
         Break;
      End
  End
  Return
End



When we execute Select * from SplitFunction('123|456|234|435','|'), we get the desired output. You may try with different inputs and delimiters.

This split function is a classic example for multi-statement table valued function in SQL server.

All Questions ASP.NET CSharp SQL Server	All Questions WCF MVC HR Round
Search This Site