TSQL String Array Workbench

Robyn and Phil show how to use XML-based arrays to make string handling easier in SQL Server 2005/2008, and illustrate the techniques with some useful functions, one of which copies the PHP str_Replace function.


  1. Introduction
  2. The array() function
  3. The PHP-style str_Replace() function
  4. The str_Find function
  5. The str_GetDelimited function


Arrays aren’t difficult in SQL Server 2005. Here’s a very simple technique that can be extended to do some remarkably complex string processing.

A while back, a friend was bemoaning the poor string handling of SQL Server. He was a PHP programmer. There is, he told us, nothing like the array handling ability of PHP. Take the str_replace function. So handy. It even takes arrays of strings so one can do quite complex string substitutions.

It got us thinking. We can do the same in SQL Server 2005 perfectly easily. It is perfectly possible to do arrays in SQL 2000, though with a bit more of a hack. If we get stuck into using XML than we can pass structures around between procedures and functions, as well as arrays.

Take the PHP example…

Let’s convert this to its SQL Server equivalent…

The first thing we need is an array() function.

–The Array() function

Here is a simple function that turns a list into an XML fragment. We choose to standardise on a root of ‘stringarray’ and call each item an ‘element’ with a sequence number and the string itself.


We now have a simple way of getting an ordered array. You can, of course, return a single element from an array:

And you can very easily turn it into a conventional SQL table:

So we’re ready for some harder stuff! Here is the Str_Replace function:

The PHP-Style str_Replace function

–The str_Find function

The str_GetDelimited function

So there we have it. Phil and I hope that we’ve given you enough to get you started. There is a lot we’ve left out as the article would have gotten rather long. We also feel slightly guilty that we have left the SQL 2000 users out of this workshop, but you can do a surprising amount of this in SQL Server 2000 just with some simple string splitting techniques (We’ve covered the basics in a previous workbench). Perhaps someone else will contribute a SQL Server 2000 version that uses Varchar (8000)s.

To cover a complete array handling scheme, we should, perhaps, have included array element deletion, insertion and update, but this is all in the XML primers, the Workbench seemed to be getting rather long, and Phil gets grumpy when I do too much FLWOR in a workbench. He says it demoralises people!

Prettified with the Simple-Talk SQL Prettifier.