Run Code
|
Code Wall
|
Users
|
Misc
|
Feedback
|
About
|
Login
|
Theme
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
Please
log in
to post a comment.
List longest unique string for each id
Loading json file into sql server
DGDFGDFG
String between two indexes
Varchar to Binary and vice-versa
Null as default value
Primary key and unique key for single column and multi columns
BT SQL Project
bc160401693
Create Date Batches from Date Range - SQL Server
Please log in to post a comment.