Wednesday, July 24, 2013

MSSQL - Split A String of Text Separated by Delimiter into A Table

/*
This SP returns a table containing rows of items that were separated by the delimiter in the given string
*/


SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE FUNCTION [dbo].[SplitMe]
(
    @List varchar(max),
    @SplitOn nvarchar(5)
)
RETURNS @RtnValue table (Line varchar(8000))
AS BEGIN    While (Charindex(@SplitOn,@List)>0)
    BEGIN
        INSERT INTO @RtnValue(Line)
        SELECT skuid = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List) - 1)))
        SET @List = Substring(@List,Charindex(@SplitOn,@List) + len(@SplitOn), len(@List))
    END
     Return
END 

No comments:

Post a Comment