Search code examples
sqlsql-servert-sqlconcatenation

SQL Server concatenate ignore null value

I am currently working on a query which join multiple columns in to 1 string with comma (,) as a separator between each other. I'm unable to figure out how to deal with null values.

DECLARE 
    @item1 nvarchar(max) = 'a',
    @item2 nvarchar(max) = 'b',
    @item3 nvarchar(max) = 'c'

SELECT CONCAT(
    ISNULL(NULLIF(@item1, '') + ', ', ''), 
    ISNULL(NULLIF(@item2, '') + ', ', ''), 
    ISNULL(NULLIF(@item3, ''), '')
)

--@item1 = 'a', @item2 = 'b', @item3 = 'c'
--Output : a, b, c

--@item1 = 'a', @item2 = NULL, @item3 = 'c'
--Output : a, c

--@item1 = NULL, @item2 = 'b', @item3 = 'c'
--Output : b, c

With the code above it works well when @item1 or @item2 even both are NULL value, but there is a problem if @item3 is NULL value, it will have an extra comma(,) at the end,

--@item1 = 'a', @item2 = 'b', @item3 = NULL
--Output : a, b,

--@item1 = 'a', @item2 = NULL, @item3 = NULL
--Output : a,

I know this is because I hard coded the comma(,) on the NULLIF statement for @item1 and @item2, but I can't figure out there is any other better way to do this.

Any advise or a better solution for this problem?

Solution

  • The STUFF method is probably the better option here:

    STUFF(CONCAT(',' + NULLIF(@item1, ''),',' + NULLIF(@item2, ''),',' + NULLIF(@item3, '')),1,1,'')