Appending messages in a variable using COALESCE function

Question

Below is my code.

DECLARE @msg NVARCHAR(MAX) = NULL

;WITH CTE AS (
                    SELECT 'A' AS Message
                UNION
                    SELECT 'B' AS Message
                UNION
                    SELECT 'C' AS Message
                UNION
                    SELECT 'D' AS Message
)

SELECT @msg = COALESCE(ISNULL(@msg,'Attachements') + ', ','') +  Message FROM CTE

SELECT @msg + ' are missing.'

It is generating output :-

Attachments, A, B, C, D are missing.

How can I avoid first comma after word "Attachments" ? Please help.

Other techniques to satisfy the requirement would also be welcome.

Thanks.

score 5 · Accepted Answer · edited Jul 29 '20 at 19:20

5

Instead of using the undocumented/unsupported way of concatenating strings, use FOR XML PATH instead:

DECLARE @msg NVARCHAR(MAX) = NULL

;WITH CTE AS (
    SELECT 'A' AS Message
    UNION
    SELECT 'B' AS Message
    UNION
    SELECT 'C' AS Message
    UNION
    SELECT 'D' AS Message
)

SELECT @msg = 'Attachments ' + 
    STUFF((
        SELECT ', ' + Message 
        FROM CTE
        ORDER BY Message
        FOR XML PATH(''), TYPE).value('.[1]', 'NVARCHAR(MAX)')
    , 1, 2, ' ')

SELECT @msg + ' are missing.'

edited Jul 29 '20 at 19:20

Community

1
1

answered Feb 24 '16 at 06:05

Felix Pamittan

31,544
7
41
67

Will go with this one considering performance. Though I dint understood it enough :( +1 – Aditya Feb 24 '16 at 06:46
And for what " TYPE).value('.[1]', 'NVARCHAR(MAX)') " is there ? Strange, I dint used data type in select code !! – Aditya Feb 24 '16 at 09:40

score 3 · Answer 2 · answered Feb 24 '16 at 06:05

3

Try this:

SELECT @msg = ISNULL(@msg + ', ', 'Attachements ') +  Message FROM CTE

and check out FOR XML approach for concatenating string

answered Feb 24 '16 at 06:05

Ivan Starostin

8,798
5
21
39

Thanks for making it simple. +1 for the simplicity :) – Aditya Feb 24 '16 at 06:47

score 1 · Answer 3 · answered Feb 24 '16 at 06:33

1

use case when

SELECT @msg = case when @msg is NULL then 'Attachment ' 
else
COALESCE(@msg + ', ','') 
end
+ Message FROM CTE
SELECT @msg + ' are missing.'

answered Feb 24 '16 at 06:33

LKW

180
1
3
13

I dint thought about including CASE in my code. :( thanks +1 – Aditya Feb 24 '16 at 06:47

score 0 · Answer 4 · answered Feb 24 '16 at 12:16

0

Just change the last two lines

SELECT @msg = COALESCE(@Msg + ', ','') +  Message FROM CTE

SELECT 'Attachments '+@msg + ' are missing.'

answered Feb 24 '16 at 12:16

mindbdev

404
3
8

Appending messages in a variable using COALESCE function

4 Answers4

Linked