How to split a comma separated string into multiple records using T-SQL function?

 Let's say you have a comma separated string like: 

split,a,comma,separated,string

 

Now you need to convert the string to follow rows:

split

a

comma

separated

string

 

Solution:

You can create a user defined UDF like the one shown below. Then, just pass in the comma separated list from another query and it will return a table with each value in a separate row. The function used here is a table valued function.

 

CREATE FUNCTION [dbo].[fnSplitStringToTable]
(
    @input nvarchar(MAX),
    @delimiter char(1) = ','
)
RETURNS
@Result TABLE
(
    Value nvarchar(MAX)
)
AS
BEGIN
    DECLARE @chIndex int
    DECLARE @item nvarchar(MAX)

    WHILE CHARINDEX(@delimiter, @input, 0) <> 0
    BEGIN
        SET @chIndex = CHARINDEX(@delimiter, @input, 0)
        SELECT @item = SUBSTRING(@input, 1, @chIndex - 1)

        IF LEN(@item) > 0
        BEGIN
            INSERT INTO @Result(Value)
            VALUES (@item)
        END
        SELECT @input = SUBSTRING(@input, @chIndex + 1, LEN(@input))
    END
    IF LEN(@input) > 0
    BEGIN
        INSERT INTO @Result(Value)
        VALUES (@input)
    END
    RETURN
END

 

Here is how you can execute the function and produce results as expected: