Splitting A String Into Rows

I have a table with a field consisting of multiple strings seperated by a '+'. Each part of the string has a length of either 2 or 3 chars. Example: 'ab+cde+fg'. Each row has 1 t

Solution 1:

The general algorithm works like this:

DECLARE input CHAR(100);
DECLARE separator_position INTEGER;

SET input ='AA+CCC+D';

CREATETABLE
   #output
(
   part CHAR(10)
);

SET separator_position =POSITION('+'IN input);

WHILE separator_position >0 DO

  INSERTINTO #output (part) VALUES (SUBSTRING(input, 1, separator_position -1));
  SET input =SUBSTRING(input, separator_position +1, 100); 

  SET separator_position =POSITION('+'IN input);
END WHILE;

INSERTINTO #output(part) VALUES (SUBSTRING(input, 1, 10));

SELECT*FROM #output;

This code will insert 3 rows AA, CCC, D into the temporary table #output.

Here is a version that works with multi character delimiters and also contains a part counter:

DECLARE@input STRING;
DECLARE@delimiter_position INTEGER;
DECLARE@delimiter STRING;

TRY DROPTABLE #output; CATCH ALLEND TRY;

SET@delimiter=CHAR(13) +CHAR(10);
SET@input='AA'+CHAR(13) +CHAR(10) +'CCC'+CHAR(13) +CHAR(10) +'D';

CREATETABLE
   #output
(
     counter AUTOINC
   , part CHAR(10)
);

SET@delimiter_position =POSITION(@delimiterIN@input);

WHILE @delimiter_position >0 DO

  INSERTINTO #output (part) VALUES (LEFT(@input, @delimiter_position -1));
  SET@input=RIGHT(@input, LENGTH(@input) - (@delimiter_position + LENGTH(@delimiter)) +1); 

  SET@delimiter_position =POSITION(@delimiterIN@input);
END WHILE;

INSERTINTO #output(part) VALUES (LEFT(@input, LENGTH(@input)));

SELECT*FROM #output;