Technical Article

Split by Row & Field delimitters

,

Function takes 3 arguments:

String, Field Delimitter, and Row Delimitter and returns a table.

This

SELECT * FROM dbo.udf_Split2('This is,aTest|of the, Split2 function','|',',')

Return this:

ID Value1 Vaue2

1 This is a Test

2 of the Split2 function

CREATE FUNCTION [dbo].[udf_Split2]
(
 @List VARCHAR(8000),
 @Delimitter VARCHAR(5),
 @SplitOn VARCHAR(5)
) 
RETURNS @RtnValue TABLE
( 
 ID INT identity(1,1),
 Value1 VARCHAR(100),
 Value2 VARCHAR(100)
) 
AS
BEGIN

    DECLARE @SubString as VARCHAR(50)
    DECLARE @Index1 AS INT
    DECLARE @Index2 AS INT

    WHILE (Charindex(@SplitOn,@List)>0)
    BEGIN

        SELECT @Index1 = Charindex(@Delimitter,@List) 
        If @Index1 = 0 Set @Index1 = 200
        SELECT @SubString = ltrim(rtrim(Substring(@List,1,@Index1-1)))
        SELECT @Index2 = Charindex(@SplitOn,@Substring)

        INSERT INTO 
            @RtnValue (Value1,Value2)
        SELECT 
             Value1 = ltrim(rtrim(Substring(@SubString,1,@Index2-1)))
            , Value2 = ltrim(rtrim(Substring(@SubString,@Index2+1,Len(@Substring)-@Index2)))

        SET @List = Substring(@List,@Index1 +len(@SplitOn),len(@List))
    END 

    RETURN
END

Rate

3.25 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.25 (4)

You rated this post out of 5. Change rating