string splitter, Sql Server

string splitter

Language: Layout:

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

View schema
Execution time: 0,02 sec, rows selected: 237, rows affected: 77, absolute service time: 0,24 sec

	id	txt
1	1	baum;hans;hund
2	2	elefant;busch;peter
3	3	gerald;kastanie;schmetterling

	id	separated
1	1	baum
2	2	hans
3	3	hund
4	4	elefant
5	5	busch
6	6	peter
7	7	gerald
8	8	kastanie
9	9	schmetterling

	id	separator	string	current_substring	startpoint	endpoint	counter	string_len	message
1	1	;	baum;hans;hund		0	14	0	NULL	start of while
2	2	;	baum;hans;hund		0	14	1	NULL	start of while
3	3	;	baum;hans;hund	b	0	14	2	NULL	start of while
4	4	;	baum;hans;hund	ba	0	14	3	NULL	start of while
5	5	;	baum;hans;hund	bau	0	14	4	NULL	start of while
6	6	;	baum;hans;hund	baum	0	14	5	NULL	start of while
7	7	;	baum;hans;hund	baum;	0	14	6	NULL	start of while
8	8	;	baum;hans;hund	h	6	14	7	NULL	start of while
9	9	;	baum;hans;hund	ha	6	14	8	NULL	start of while
10	10	;	baum;hans;hund	han	6	14	9	NULL	start of while
11	11	;	baum;hans;hund	hans	6	14	10	NULL	start of while
12	12	;	baum;hans;hund	hans;	6	14	11	NULL	start of while
13	13	;	baum;hans;hund	h	11	14	12	NULL	start of while
14	14	;	baum;hans;hund	hu	11	14	13	NULL	start of while
15	15	;	baum;hans;hund	hun	11	14	14	NULL	start of while
16	16	;	elefant;busch;peter		0	19	0	NULL	start of while
17	17	;	elefant;busch;peter		0	19	1	NULL	start of while
18	18	;	elefant;busch;peter	e	0	19	2	NULL	start of while
19	19	;	elefant;busch;peter	el	0	19	3	NULL	start of while
20	20	;	elefant;busch;peter	ele	0	19	4	NULL	start of while
21	21	;	elefant;busch;peter	elef	0	19	5	NULL	start of while
22	22	;	elefant;busch;peter	elefa	0	19	6	NULL	start of while
23	23	;	elefant;busch;peter	elefan	0	19	7	NULL	start of while
24	24	;	elefant;busch;peter	elefant	0	19	8	NULL	start of while
25	25	;	elefant;busch;peter	elefant;	0	19	9	NULL	start of while
26	26	;	elefant;busch;peter	b	9	19	10	NULL	start of while
27	27	;	elefant;busch;peter	bu	9	19	11	NULL	start of while
28	28	;	elefant;busch;peter	bus	9	19	12	NULL	start of while
29	29	;	elefant;busch;peter	busc	9	19	13	NULL	start of while
30	30	;	elefant;busch;peter	busch	9	19	14	NULL	start of while
31	31	;	elefant;busch;peter	busch;	9	19	15	NULL	start of while
32	32	;	elefant;busch;peter	p	15	19	16	NULL	start of while
33	33	;	elefant;busch;peter	pe	15	19	17	NULL	start of while
34	34	;	elefant;busch;peter	pet	15	19	18	NULL	start of while
35	35	;	elefant;busch;peter	pete	15	19	19	NULL	start of while
36	36	;	gerald;kastanie;schmetterling		0	29	0	NULL	start of while
37	37	;	gerald;kastanie;schmetterling		0	29	1	NULL	start of while
38	38	;	gerald;kastanie;schmetterling	g	0	29	2	NULL	start of while
39	39	;	gerald;kastanie;schmetterling	ge	0	29	3	NULL	start of while
40	40	;	gerald;kastanie;schmetterling	ger	0	29	4	NULL	start of while
41	41	;	gerald;kastanie;schmetterling	gera	0	29	5	NULL	start of while
42	42	;	gerald;kastanie;schmetterling	geral	0	29	6	NULL	start of while
43	43	;	gerald;kastanie;schmetterling	gerald	0	29	7	NULL	start of while
44	44	;	gerald;kastanie;schmetterling	gerald;	0	29	8	NULL	start of while
45	45	;	gerald;kastanie;schmetterling	k	8	29	9	NULL	start of while
46	46	;	gerald;kastanie;schmetterling	ka	8	29	10	NULL	start of while
47	47	;	gerald;kastanie;schmetterling	kas	8	29	11	NULL	start of while
48	48	;	gerald;kastanie;schmetterling	kast	8	29	12	NULL	start of while
49	49	;	gerald;kastanie;schmetterling	kasta	8	29	13	NULL	start of while
50	50	;	gerald;kastanie;schmetterling	kastan	8	29	14	NULL	start of while
51	51	;	gerald;kastanie;schmetterling	kastani	8	29	15	NULL	start of while
52	52	;	gerald;kastanie;schmetterling	kastanie	8	29	16	NULL	start of while
53	53	;	gerald;kastanie;schmetterling	kastanie;	8	29	17	NULL	start of while
54	54	;	gerald;kastanie;schmetterling	s	17	29	18	NULL	start of while
55	55	;	gerald;kastanie;schmetterling	sc	17	29	19	NULL	start of while
56	56	;	gerald;kastanie;schmetterling	sch	17	29	20	NULL	start of while
57	57	;	gerald;kastanie;schmetterling	schm	17	29	21	NULL	start of while
58	58	;	gerald;kastanie;schmetterling	schme	17	29	22	NULL	start of while
59	59	;	gerald;kastanie;schmetterling	schmet	17	29	23	NULL	start of while
60	60	;	gerald;kastanie;schmetterling	schmett	17	29	24	NULL	start of while
61	61	;	gerald;kastanie;schmetterling	schmette	17	29	25	NULL	start of while
62	62	;	gerald;kastanie;schmetterling	schmetter	17	29	26	NULL	start of while
63	63	;	gerald;kastanie;schmetterling	schmetterl	17	29	27	NULL	start of while
64	64	;	gerald;kastanie;schmetterling	schmetterli	17	29	28	NULL	start of while
65	65	;	gerald;kastanie;schmetterling	schmetterlin	17	29	29	NULL	start of while