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.
Subscribe to:
Post Comments (Atom)
pls write it in C#
ReplyDeleteIn c# you can just use string.split('|') method...that will give you string array back with all the split members
DeleteIn C#, this is the code.
Deletestring s = "123|456|234|435";
string[] star = s.Split('|');
foreach (string word in star)
{
Response.Write(word+"
");
}
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.
ReplyDeleteOriginal String
Token A^*!%~Token B^*!%~Token C^*!%~Token D^*!%~Token E
Output String
Token E^*!%~Token D^*!%~Token C^*!%~Token B^*!%~Token A
using System;
Deleteusing 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();
}
}
}