Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Assuming your data contains plain alpha-numeric characters and nothing to trip up XML, you can use this query with performs some text manipulation to make your data look like XML, and then performs XQuery to slice up the column.

The code is a bit more complex, but performance is significantly better, especially as the number of rows you need to parse increases.

The first several CTEs (E1, E2, and E4) are just setting up the tally table. The cteTally CTE then dynamically generates the optimal tally table length (the length of the longest string in your table). Then, cteStart finds all of the separation points. The result for your data set looks like:

Interpreting this is a little tricky because the process adds a pseudo-delimiter (if one does not exist already) at the first position, and this is a one-indexed string. Thus, the N1 = 1 for rows 1, 2, and 3. 3 has no more delimiters. 1 has extra delimiters at positions 3, 5, 7, and 10, again noting that character "a" is actually at position 2 in this setup.

Anyhow, the final query does the actual splitting and returns your desired data set.

The tally table split is the best non-CLR solution for the string-splitting problem. If you have fewer than about 10,000 rows, the CTE tally table is faster, but after about that 10K mark, the standard tally table solution becomes better. A CLR string splitting module would be faster than both, but I know that a lot of shops are hesitant to install CLR modules.