Turning Strings into Tables

EDITOR'S NOTE: Share your T-SQL tips and tricks with other T-SQL Solutions readers. Email your ideas (400 words or fewer) to [email protected]. If we print your submission, you'll get $50.

In my SQL Server Magazine article "Treat Yourself to Fn_Split()," I described fn_Split(), a user-defined function (UDF) that accepts a delimited string as a first parameter and the delimiter as a second parameter and that returns an indexed table of values. You can use this UDF to emulate variable-length parameter arrays in T-SQL programs. The table that fn_Split() returns has only two columns, one for the index and one for the values in the string. The table emulates the one-dimensional string array that the Visual Basic (VB) Split() function returns.

Shortly after writing that article, I wondered how I could convert a delimited string into a table with more than two columns. On the client side, you can use the VB GetString() method of the ADO Recordset object to transform an ADO recordset into a delimited string. You could then pass the string as a parameter to a SQL Server stored procedure, transform the string into a table, and use one INSERT statement to insert the table into a database.

Typically, you insert recordsets from the client one row at a time, calling a stored procedure for each row. If you want to use only one transaction to insert the rows, you have to use ADO to initiate the transaction on the client. However, if you submit the entire recordset to a single stored procedure as one delimited string, you can reduce network traffic and wrap the changes to your database in a transaction that you initiate on the server.

This type of process is useful in such business applications as remote order entry, for example. A client can send the server a master record with one extra field containing all the order details in a delimited string—all in one trip instead of several. The order details field contains line items as substrings separated by row delimiters. Each line item contains part number, unit price, and quantity substrings separated by field delimiters.

Let's look at how to transform a string into a table and use one INSERT statement to insert the table into a database. You can leverage the fn_Split() function to create another custom table-valued function, fn_StringToTable(). This function accepts a delimited string and returns a table that has as many columns as you need. You can then wrap fn_StringToTable() in a simple stored procedure that inserts the returned table into the database.

The fn_StringToTable() function, which Listing 1 shows, returns a four-column table. The input parameter, @string, uses commas for column delimiters and semicolons for row delimiters. The following delimited string is an example of input:

'2,3.50,Mary,6/12/2001; 4,8.70,John,6/10/2001; 20,10.50,Jennifer,6/11/2001'

Fn_StringToTable() uses fn_Split() to split the input string at the semicolon delimiters, creating an intermediate table of comma-delimited substrings. Fn_StringToTable() then splits each comma-delimited substring into four columns and inserts them into the return table.

The code at callout A in Listing 1 defines the four-column return table and declares a table variable, @a, to hold the intermediate table. SQL Server 2000 UDFs don't allow temporary tables, so you must use a table variable instead.

The code at callout B invokes fn_Split() to transform the original string into the intermediate table of substrings, then inserts the substrings into @a. Table 1 shows the contents of @a when you use the sample delimited string above.

The code at callout C assigns to the @maxidx variable the maximum value in the idx column of table @a. Then, @maxidx starts a WHILE loop. Within the WHILE loop, another call to fn_Split()—within a SELECT CASE statement—parses each delimited substring into columns. The loop then inserts those column values into the function's return table. Table 2 shows the final contents of the return table.

How does this technique work? Table 3 shows the result you get when you invoke fn_Split() on the first @value string. You can move the four values into separate columns by using a case structure like this:

SELECTCASE WHEN idx = 0 THEN Convert(int, value) END,CASE WHEN idx = 1 THEN Convert(money, value) ENDCASE WHEN idx = 2 THEN Convert(varchar(50), value) ENDCASE WHEN idx = 3 THEN Convert(smalldatetime, value) ENDFROM dbo.fn_Split(@value,',')

Table 4 shows the result that this case structure produces—a four-column table with NULL values in all but the diagonal cells.

To flatten Table 4 into one row, you use either the MAX() or MIN() aggregate function. For a complete description of this flattening technique, see Kenneth W. Henderson, The Guru's Guide to Transact-SQL (Addison-Wesley Longman, 2000), Chapter 10. MAX() and MIN() are the only aggregate functions that you can use on non-numeric data. The aggregate functions ignore the NULLs and pick the maximum or minimum of the remaining values. Because only one non-NULL value exists in each column, either the MAX() or the MIN() function works. To wrap the fn_StringToTable() function in a simple stored procedure that inserts the return table into the database, use the code that Listing 2 shows.

—Burton Roberts
[email protected]