SQL: String Concatenation Using FOR XML PATH(”)


Every so often I have the need to concatenate a column of strings returned from a SQL query. I have always used the COALESCE function to do this, something like the following:

SELECT @names = COALESCE(@names + ',', '') + FirstName 
FROM Residents

I looked up the documentation for COALESCE and realized that it is supposed to be used to return the first nonnull expression among those passed to it. While this works for concatenation, using it in such a fashion is more of a hack rather that what it is actually supposed to be use for.

With the introduction of SQL Server 2005, we are able to combine both SQL and XML much easier. Among the new features is the FOR XML PATH function that is intended for string concatenation when generating XML.

SELECT ', ' + FirstName 
FROM Resident
FOR XML PATH ('')

-- Output
-- ------
-- , Shireda, William, Melissa, Kelly, Clarence

To get rid of the leading comma and space we can use the STUFF function. From MSDN:

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

STUFF (character_expression , start , length ,character_expression)

The final code becomes:

SELECT STUFF((SELECT ', ' + FirstName 
			  FROM Resident
			  FOR XML PATH ('')), 
			  1, 2, '')

-- Output
-- ------
-- Shireda, William, Melissa, Kelly, Clarence

Some may argue that using the FOR XML PATH function for string concatenation is just as much as a hack as using the COALESCE function as we aren’t actually generating XML but using the FOR XML PATH function seems a bit more intuitive to me.

Posted in SQL. Tags: . 1 Comment »

One Response to “SQL: String Concatenation Using FOR XML PATH(”)”

  1. John B. Seals (@sparky) Says:

    This is great! But when I do it the resulting column is a “CLOB.” Any ideas as to why?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: