A while ago, when I was working in a company with my friend Omid Mafakher, he developed a table function which works just link split function in other languages such as VB.NET, C#, C++, etc.
After I get permission from Omid, I decided to publish it as a blog post. Here's the function:
-- This stored procedure splits a string value with the specified delimiter.
-- Copyright 2007 Omid Mafakher.
Create Function [dbo].[Split] (@String nvarchar(MAX), @Delimiter char(1))
Returns @Results Table (Items nvarchar(MAX))
As
Begin
Declare @Index int
Declare @Slice nvarchar(4000)
Set @Index = 1
If @String Is NULL Return
While @Index != 0 Begin
Select @Index = CharIndex(@Delimiter, @String)
If (@Index != 0) Begin
Select @Slice = left(@String, @Index - 1)
End else Begin
Select @Slice = @String
End
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)
If Len(@String) = 0 break
End
Return
End
SplitFunction.sql (1.38 kb)
144dd169-8f2a-4fbe-955a-5c5b932a229f|0|.0