How to get a comma-separated single address from a SQL Server database where some elements might be NULL

Question

So this is a pretty standard requirement; I have a table holding address information and I want to pull out a single, "formatted" address with all the fields concatenated with comma separators.

The problem is that some fields can be NULL and so I end up with trailing commas. For example, one address might be "10 The Strand, London" with no country on the end but the next one might be "5 Fleet Street, London, England". If I pick out each address element and assume it is always present then I would present these two addresses as:

"5 Fleet Street, , , London,"

"10 The Strand, , , London, England"

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

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

Here is an example using a 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;

Which 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 want this? Partly because I can't think of a way to do this but "feel" there should be a way to get what I want and partly because this query is running over a linked server to a SQL 2000 box and so I can't use CTEs (although I could easily rewrite the query using a sub query instead).

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 that is not null, so for example if Line1 is null, then '' will be returned (otherwise ,Line1).

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

Results against the 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 a '' (empty string). I added the ', ' as a delimiter to the value before testing to see if it is NULL or not. This way if the column is null then the column+delimiter is also NULL and the ISNULL test still returns a ''. I put the delimiter before the column value to make it easier to remove the extra delimiter. Had the extra delimiter been at the end of the string then I would have had to use a LEN function or something similar to calculate where the extra delimiter was. This way it is always at the beginning of the string and by using the STUFF function I could replace the first 2 characters with a '' effectively removing 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