concatenate strings and coalesce

Question

I have a query that selects rows from three different tables: TableA, TableB and TableC.

One field of my query has to be the concatenation of the field Name in each table like the following alias:

SELECT
    A.Name,
    B.Name,
    C.Name,
    CONCAT(A.Name, ' / ', B.Name, ' / ', C.Name) AS Full_name
FROM ... 

The query joins the three tables using a LEFT OUTER JOIN. In some cases I get odd results if one of the table does not have entries as the following table:

--------------------------
|Table A |Table B|Table C|
--------------------------
|My      |Name   |       |
--------------------------
|My      |       |Is     |
--------------------------
|My      |Name   |Is     |
--------------------------

And the trailing / are visible like this one:

1) My / Name /
2) My / / Is
3) My / Name / Is

I should generate the following results but I do not want to use CASE so I was wondering if I can play with COALESCE, ISNULL and CONCAT all together

Expected Result

1) My / Name
2) My / Is
3) My / Name / Is

Answer 1

When adding ' / ' and Name, you get null if name is null. Therefore you can remove them simply by adding.

Try this:

SELECT
    A.Name,
    B.Name,
    C.Name,
    CONCAT(A.Name, ' / '+ B.Name, ' / '+ C.Name) AS Full_name
FROM 
...

Sample in Fiddle.

Answer 2

The approach I would go for is:

STUFF(CONCAT(' / ' + A.Name, ' / ' + B.Name, ' / ' + C.Name), 1, 3, '')

The premise being if any of your values are NULL then ' / ' + NULL will also be null, but by appending ' / ' to the start of everything, then you know you will always end up with / at the start, so you can safely remove the first 3 characters with the STUFF function.

A slightly extended example would be

SELECT  *,
        [Concat] = STUFF(CONCAT(' / ' + A, ' / ' + B, ' / ' + C, ' / ' + D), 1, 3, '')
FROM    (VALUES
            ('A', 'B', NULL, 'D'),
            ('A', NULL, 'C', NULL),
            ('A', 'B', 'C', NULL),
            ('A', 'B', 'C', 'D'),
            ('A', NULL, NULL, 'D'),
            (NULL, NULL, NULL, NULL),
            (NULL, 'B', 'C', NULL)
        ) T (A, B, C, D);