CODEWITHSUNDEEP
sqlsql-servert-sqlconcatenation
Dean
Dean

Reputation: 678

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?

Upvotes: 5

Views: 2992

Answers (3)

bwperrin
bwperrin

Reputation: 692

Simpler than STUFF, use CONCAT_WS:

CONCAT_WS(', ', NULLIF(@item1,''), NULLIF(@item2,''), NULLIF(@item3,''))

Upvotes: 2

Sarvesh Bandekar
Sarvesh Bandekar

Reputation: 96

Here you can use, the concat operator, for the first variable don't pass concat separator, from second variable onwards pass the concat variable (separator) before variable...

declare @a varchar(10)='a'
declare @b varchar(10)='b'
declare @c varchar(10)=null
select isnull(@a,'')+isnull(','+@b,'')+isnull(','+@c,'')

Upvotes: 0

Thom A
Thom A

Reputation: 95557

The STUFF method is probably the better option here:

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

Upvotes: 9

Related Questions