Split function





5.00/5 (1 vote)
Split User Defined Function, MS SQL Server 2008
This is MS SQL Server 2008 adopted Split function (UDF)
Source: Best Split UDF function with delimeter in T-SQL to return result as a table
--DROP Function [dbo].[fnStringSplitter]
CREATE Function [dbo].[fnStringSplitter]
(
@IDs Varchar(max) --A big string which may have delimeter in it or not
,@Delimiter Varchar(1) -- Delimeter to use for splitting up the given string
)
/*********************** RETURN *********************/
--Returns the table with specific values in a temporary table. Useful especially if you have any IDs in the
--given string and want to get them as a table row values.
--It is handy for multi selection parameters in SQL Server Reporting Services
-- Example:
--@IDs = 1,2,3,4,5,6,7,8,9,10
--@Delimeter = ','
--Returns @Tbl_IDS, which is having 10 rows with above IDS in each row by splitting up with given delimeter [in this example ',']
/****************************************************/
Returns @Tbl_IDs Table (ID Varchar(500)) As
Begin
-- Append comma
Set @IDs = @IDs + @Delimiter
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
Declare @RowNum Int
-- Start from first character
Set @Pos1 = CharIndex(@Delimiter,@IDs,1)
Set @Pos2=1
While @Pos1 > 0
Begin
Insert @Tbl_IDs Values (Substring(@IDs,@Pos2,@Pos1-@Pos2))
-- Go to next non comma character
Set @Pos2=@Pos1+1
-- Search from the next charcater
Set @Pos1 = CharIndex(@Delimiter,@IDs,@Pos1+1)
End
Return
End
Test
SELECT * from dbo.fnStringSplitter(',,1,2,3,4,5,6,7,8,9,10', ',')