I ran into a scenario where i was informed that “Table-valued functions are a pain to use because you need to use a cursor”. The problem was that the individual had a blob of text that they wished to break up using a set delimiter, and they wished to use TSQL. Since I usually reply to questions with an answer, and a “also remove the cursor, please”, developers have become accustomed to writing queries without cursors. So, I suppose this was a topic that needed to get some attention at some point.

The answer to this dilemma is that you certainly can use a UDF to break up the blob of text and return the information without employing the use of a cursor. To do this, we can use CROSS APPLY. This will break up the data and return the relevant rows for us.

First, we create a UDF, in this case, a splitter function. There are examples of these everywhere, I threw this one together quickly. Pass in some text and a single character delimiter, get back the split up results – you get the idea.

In this case, we have at least one value returned for each of the text blobs, so we will not have any NULL data sets returned from the UDF. If we were expecting NULLs, we would use OUTER APPLY instead of CROSS APPLY.