sqlsql-servercsvsql-server-2008

How to split a comma-separated value to columns


I have a table like this

Value String
1 Cleo, Smith

I want to separate the comma delimited string into two columns

Value Name Surname
1 Cleo Smith

I need only two fixed extra columns


Solution

  • CREATE FUNCTION [dbo].[fn_split_string_to_column] (
        @string NVARCHAR(MAX),
        @delimiter CHAR(1)
        )
    RETURNS @out_put TABLE (
        [column_id] INT IDENTITY(1, 1) NOT NULL,
        [value] NVARCHAR(MAX)
        )
    AS
    BEGIN
        DECLARE @value NVARCHAR(MAX),
            @pos INT = 0,
            @len INT = 0
    
        SET @string = CASE 
                WHEN RIGHT(@string, 1) != @delimiter
                    THEN @string + @delimiter
                ELSE @string
                END
    
        WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
        BEGIN
            SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
            SET @value = SUBSTRING(@string, @pos, @len)
    
            INSERT INTO @out_put ([value])
            SELECT LTRIM(RTRIM(@value)) AS [column]
    
            SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
        END
    
        RETURN
    END