string splitter, Sql Server

string splitter

CREATE TABLE test (id INT NOT NULL PRIMARY KEY IDENTITY(1,1), 
                   txt NVARCHAR(MAX)
                  );
CREATE TABLE separatedValues (id INT NOT NULL PRIMARY KEY IDENTITY(1,1), 
                              separated NVARCHAR(MAX)
                             );
CREATE TABLE debug (id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
                    separator NVARCHAR(MAX), 
                    string NVARCHAR(MAX),
                    current_substring NVARCHAR(MAX),
                    startpoint INT, 
                    endpoint INT,
                    counter INT, 
                    string_len INT,
                    message NVARCHAR(MAX)
                   );

INSERT INTO test (txt) VALUES ('baum;hans;hund'),('elefant;busch;peter'),('gerald;kastanie;schmetterling')
GO

-- Diese funktion füllt eine tabelle separatedValues (welche bereits angelegt wurde) mit werten.
-- SeparatedValues sollte folgende Eigenschaften haben: id (auto increment und primary key) und 
CREATE PROCEDURE fillSeparatedValues(@string NVARCHAR(MAX))
AS
BEGIN
    DECLARE @separator VARCHAR(1)
    DECLARE @startpoint INT
    DECLARE @endpoint INT 
    DECLARE @counter INT
    DECLARE @current_substring_length INT
    
    -- SEPARATOR kann angepasst werden mit anderem Separator
    SET @separator = ';'
    SET @startpoint = 0
    SET @endpoint = LEN(@string)
    
    SET @counter = 0
    SET @current_substring_length = 0
    
    WHILE @counter <= @endpoint
    BEGIN    
        INSERT INTO debug (separator,string,current_substring,startpoint,endpoint,counter,message) VALUES
                             (@separator,@string,SUBSTRING(@string, @startpoint, @current_substring_length),@startpoint,@endpoint,@counter,'start of while')
        IF ((RIGHT(SUBSTRING(@string, @startpoint, @current_substring_length), 1) = @separator))
        BEGIN
            INSERT INTO separatedValues (separated) VALUES (SUBSTRING(@string, @STARTPOINT, @current_substring_length - 1))
            SET @startpoint = @counter
            SET @current_substring_length = 0
        END
        ELSE
        BEGIN
            IF (@counter = @endpoint)
            BEGIN
                INSERT INTO separatedValues (separated) VALUES (SUBSTRING(@string, @STARTPOINT, @current_substring_length + 1))
            END
        END
        -- WENN LETZTER CHARAKTER -> not impl. yet
        SET @counter = @counter + 1
        SET @current_substring_length = @current_substring_length + 1
    END
END
GO

DECLARE @stringToSplit NVARCHAR(MAX)

SET @stringToSplit = (SELECT txt FROM test WHERE id = 1)
EXEC fillSeparatedValues @stringToSplit

SET @stringToSplit = (SELECT txt FROM test WHERE id = 2)
EXEC fillSeparatedValues @stringToSplit

SET @stringToSplit = (SELECT txt FROM test WHERE id = 3)
EXEC fillSeparatedValues @stringToSplit
GO

SELECT * FROM test
SELECT * FROM separatedValues 
SELECT * FROM debug


run \| edit \| history \| help	0