Using Coalesce For Comma Delimited List

Question

Coalesce will return the first non-null value among its arguments documentation says. I can also use it like below:

DECLARE @ColumnValue VARCHAR(MAX);
SELECT @ColumnValue = COALESCE(@ColumnValue+',','') + ColumnValue
FROM dbo.TableA
SELECT @ColumnValue

According to my understanding, there should be a , at the very front of the output list since at the very beginning there will at least be a comma passed as an argument. Also, if I put some value in the second argument. It appears at the very front instead of the , as I expected.

If someone can please explain the code to me. I will be very grateful.

You better explain clearly what are you trying to achieve and illustrate it with sample data — peterm, Jul 22 '13 at 20:49
If @ColmnValue is null and you are adding ',' to that, then the result is still going to be NULL, in which case you will get the next value in coalesce being '' — Petio Ivanov, Jul 22 '13 at 20:56
Thank you. I did not know that SQL string concatenation behaves so differently. — lbrahim, Jul 22 '13 at 22:29

score 9 · Accepted Answer · answered Jul 22 '13 at 20:55

9

Your understanding is not correct.

Focus on the expression:

SELECT @ColumnValue = COALESCE(@ColumnValue+',','') + ColumnValue
-------------------------------^XXXXXXXXXXXXXXX

The first argument to COALESCE() is @ColumnValue+','. This is NULL initially, so it gets replaced by ''. Then comes ColumnValue as the first element in the list.

You are likely thinking of:

SELECT @ColumnValue = COALESCE(@ColumnValue, '') + ',' + ColumnValue

answered Jul 22 '13 at 20:55

Gordon Linoff

1,242,037
58
646
786

1

Thank you sir for taking the trouble to answer my question. Very insightful answer. – lbrahim Jul 22 '13 at 22:07

score 6 · Answer 2 · answered Jul 22 '13 at 20:55

You are confused. Since at first @ColumnValue is NULL, then the result of @ColumnValue + ',' is also NULL, so the result of COALESCE(@ColumnValue+',','') is ''. This is easily tested with:

DECLARE @ColumnValue VARCHAR(MAX);

SELECT COALESCE(@ColumnValue+',','')

Using Coalesce For Comma Delimited List

2 Answers2