Support us MVC ASP.NET C# SQL Server WCF Written Test HR Round Subscribe C# Videos C# Programs Buy DVD

Reusable SQL function to split a string

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.

5 comments:

  1. Replies
    1. In c# you can just use string.split('|') method...that will give you string array back with all the split members

      Delete
    2. In C#, this is the code.

      string s = "123|456|234|435";

      string[] star = s.Split('|');
      foreach (string word in star)
      {
      Response.Write(word+"
      ");
      }

      Delete
  2. Please write a sample program that parses the string into a series of substrings where the delimiter between the substrings is ^*!%~ and then reassembles the strings and delimiters into a single new string where each of the substrings is in the reverse order from the original string. The method must return the final string.

    Original String
    Token A^*!%~Token B^*!%~Token C^*!%~Token D^*!%~Token E

    Output String
    Token E^*!%~Token D^*!%~Token C^*!%~Token B^*!%~Token A

    ReplyDelete
    Replies
    1. using System;
      using System.Text;
      namespace GenericsSample
      {
      class Program
      {
      static void Main()
      {
      string strOriginalString = "Token A^*!%~Token B^*!%~Token C^*!%~Token D^*!%~Token E";
      string[] strSeperator = new string[1];
      strSeperator[0] = "^*!%~";

      string[] strArrayIndividualStrings = strOriginalString.Split(strSeperator, StringSplitOptions.RemoveEmptyEntries);

      int intLengthOfStringArray = strArrayIndividualStrings.Length;

      StringBuilder sbOutputString = new StringBuilder();
      for (int i = (intLengthOfStringArray - 1); i >= 0; i--)
      {
      sbOutputString.Append(strArrayIndividualStrings[i] + strSeperator[0]);
      }
      Console.WriteLine("Original String : " + strOriginalString);
      Console.WriteLine("Output String : " + sbOutputString.ToString());
      Console.ReadLine();
      }
      }
      }

      Delete

If you are aware of any other asp.net questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us