Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

How to split a string into a list of values

Convert a string to list of values in table with one column

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Jul 31, 2022·

1 min read

Subscribe to my newsletter and never miss my upcoming articles

Play this article

I have created a function to split a string based on the delimiter value and return the result as a single column of values in a table.

CREATE OR ALTER FUNCTION dbo.fn_string_to_list (
    @input_string NVARCHAR(MAX), 
    @delimiter NVARCHAR(10)
)
RETURNS @return_table TABLE (word NVARCHAR(MAX) NULL)
AS
BEGIN
    DECLARE @position INT = 0;
    DECLARE @next_position INT = 1;
    DECLARE @word_length INT;

    WHILE @next_position > 0
    BEGIN
        SET @next_position = CHARINDEX(@delimiter, @input_string, @position + 1);
        SET @word_length = (CASE WHEN @next_position > 0 THEN @next_position ELSE LEN(@input_string) + 1 END) - @position - 1;
        INSERT INTO @return_table (word) VALUES (LTRIM(RTRIM(SUBSTRING(@input_string, @position + 1, @word_length))));
        SET @position = @next_position;
    END

    RETURN;
END

GO

Sample output: image.png

Impressum