The Xaml Workshop: Converting Delimited Data Into a Table

In this post we will examine the powerful potential of Table-valued Functions!

As per usual, all bold keywords and pertinent MSDN articles are linked at the bottom of the post.

To setup the scenario for this example, you can probably think of several instances when you pass a comma or semi-colon delimited list of values into a Stored Procedure, and need to parse each value separately. This function will do just that, but in a re-usable, dynamic way.

To begin, lets open Microsoft SQL Server Management Studio. For this example, I am using SQL Server 2008, but the principles can be transferred to most any RDBMS. Let's begin by connecting to the appropriate server and database. Let's expand the Programmability tree and the Functions tree.

We can right-click the Table-valued Functions tree and select "New Multi-statement Table-valued Function..." from the dropdown menu. This will open a new query tab, with some templated content, as seen below.

Obviously, there are quite a few invalid statements and code, but we need to replace it all with our real content. Let's begin by removing the first 10 lines of comments, and optionally the Author comment lines. Now we need to give our new function a name, such as ParseSingleDelimitedString. I placed mine in the dbo schema, but you may need to specify another if your circumstance warrants it. And, of course, leaving the schema name blank in the function name will place it automatically in the dbo schema.

For a base essentials function, you could just specify one parameter, @String, that contains the delimited data to be parsed. For my function, I also included @Delimiter, @IgnoreBlankValues and @TreatBlankAsNull. These can be hard-coded, but I wanted to make the function as dynamic as possible.

In our function template, we also need to specify the table structure we will be returning. In my example, I included a Primary Key with an Identity value, but this is completely optional. So, up to now, my function looks like below.

Once the signature of the function has been completed, we can now move on to the body, where the real magic is done. The work that the body does is not complicated, but does require some thought. The logic of it is this: Loop through the string and select the left-most characters until we find the delimiter character. Add it to our table and remove the current iteration from the string data and repeat until the string is of 0 length. Naturally, there is a little more to it than that, but it gives us a base for what our function body should look like.

For my function, I put the @String parameter into a local variable and do all my manipulation on that one. I also created an additional variable, @CurrentValue, that will be used to contain the current iteration. Below is the entire function body, which we will step through.

The loop, a WHILE statement, simply makes sure that we are working with a valid string, not with empty data.

The next step in my function is to determine if a blank value was passed - signified by a delimiter character being the left-most character. If so, we can handle it as desired. In my function, how it is handled is based on a parameter.

The next step is to determine if our delimiter exists in the string data. If so, we can parse the left-most string data into our current value, or if it doesn't exist, we can assume that the string contains the last of the delimited values. Either way, we can set our current variable and proceed.

Our last step is to add the value to the table we are returning. My function does some blank value logic, but that is technically optional.

So, there you go. Execute the code, and then test it by calling it, as per the code below. The results are sweet, especially because you can use the function as part of a SELECT statement on in a WHERE clause!