How to get a separate comma address from a SQL Server database where some items may be NULL

Question

So this is a pretty standard requirement; I have a table containing address information, and I want to pull out one “formatted” address with all the fields combined with comma delimiters.

The problem is that some fields may be NULL, and so I end up with commas. For example, one address could be “10 The Strand, London” without any country at the end, but the next could be “5 Fleet Street, London, England.” If I select each address element and assume that it is always present, I would present these two addresses as:

"5 Fleet Street ,, London,

"10 The Strand ,, London, England"

Getting rid of extra commas from the middle is simple, just a test for NULL.

I know how to fix the problem with the final comma in two passes using CTE or a subquery, but can this be done in one pass through the data?

Here is an example of using CTE:

DECLARE @Address TABLE ( Name VARCHAR(255), Line1 VARCHAR(255), Line2 VARCHAR(255), Line3 VARCHAR(255), City VARCHAR(255), Country VARCHAR(255)); INSERT INTO @Address VALUES ('Complete', 'Test 1', 'Test 2', 'Test 3', 'Oxford', 'England'); INSERT INTO @Address VALUES ('Incomplete', '22 Accacia', NULL, NULL, 'York', 'England'); INSERT INTO @Address VALUES ('Missing End', '10 Bond Street', NULL, NULL, 'London', NULL); WITH Addresses AS ( SELECT CASE WHEN Name IS NULL THEN '' ELSE Name + ', ' END + CASE WHEN Line1 IS NULL THEN '' ELSE Line1 + ', ' END + CASE WHEN Line2 IS NULL THEN '' ELSE Line2 + ', ' END + CASE WHEN Line3 IS NULL THEN '' ELSE Line3 + ', ' END + CASE WHEN City IS NULL THEN '' ELSE City + ', ' END + CASE WHEN Country IS NULL THEN '' ELSE Country + ', ' END AS [Address] FROM @Address) SELECT LEFT([Address], LEN([Address]) - 1) AS [Address Clean] FROM Addresses; 

What gives me:

 Complete, Test 1, Test 2, Test 3, Oxford, England Incomplete, 22 Accacia, York, England Missing End, 10 Bond Street, London 

Why do I need it? Partly because I cannot think of a way to do this, but “feeling” should be a way to get what I want, and partly because this request is executed through a linked server with a SQL 2000 mailbox, and therefore I cannot use CTE ( although I could easily rewrite the request using a sub request).

Answer 1

Use coalesce , for example:

 WITH Addresses AS ( SELECT coalesce(Name, '') + coalesce(', ' + Line1, '') + coalesce(', ' + Line2, '') + coalesce(', ' + Line3, '') + coalesce(', ' + City, '') + coalesce(', ' + Country, '') AS [Address] FROM @Address) SELECT Address FROM Addresses 

This will return the first parameter, which is not null, for example, if Line1 is null, then the character will be returned '' (otherwise ,Line1 ).

Note that for this, CONCAT_NULL_YIELDS_NULL must be set to ON .

Results versus test data:

 Complete, Test 1, Test 2, Test 3, Oxford, England Incomplete, 22 Accacia, York, England Missing End, 10 Bond Street, London 

Answer 2

I did a couple of things here. First I used ISNULL to determine if the value was NULL , and if so, return '' (empty string). I added ', ' as a separator for the value before testing to see if it is NULL or not. Thus, if the column is zero, the column separator + is also NULL , and the ISNULL test returns. '' I put a separator in front of the column value to make it easier to remove the extra separator. If the extra separator were at the end of the line, I would have to use the LEN function or something similar to figure out where the extra separator was added. Thus, it is always at the beginning of the line and with the help of the STUFF function I could replace the first 2 characters with '' , effectively deleting them.

 DECLARE @Address TABLE ( Name VARCHAR(255), Line1 VARCHAR(255), Line2 VARCHAR(255), Line3 VARCHAR(255), City VARCHAR(255), Country VARCHAR(255)); INSERT INTO @Address VALUES ('Complete', 'Test 1', 'Test 2', 'Test 3', 'Oxford', 'England'); INSERT INTO @Address VALUES ('Incomplete', '22 Accacia', NULL, NULL, 'York', 'England'); INSERT INTO @Address VALUES ('Missing End', '10 Bond Street', NULL, NULL, 'London', NULL); SELECT STUFF( ISNULL(', '+Name,'') + ISNULL(', '+Line1,'') + ISNULL(', '+Line2,'') + ISNULL(', '+Line3,'') + ISNULL(', '+City,'') + ISNULL(', '+Country,'') ,1,2,'') FROM @Address