SQL Server concatenate ignore null value

Question

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?

See stackoverflow.com/posts/comments/93273211

Salman Arshad
– Salman Arshad

2018-11-08 10:24:25 +00:00
Commented Nov 8, 2018 at 10:24 — Salman Arshad
– Salman Arshad, Commented Nov 8, 2018 at 10:24

Thom A · Accepted Answer · 2018-11-08 10:00:50Z

9

The STUFF method is probably the better option here:

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

answered Nov 8, 2018 at 10:00

Thom A♦

97.6k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bwperrin · Accepted Answer · 2024-02-15 19:38:51Z

2

Simpler than STUFF, use CONCAT_WS:

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

edited Feb 15, 2024 at 19:38

answered Aug 4, 2022 at 20:58

bwperrin

7025 silver badges12 bronze badges

Comments

Thom A · Accepted Answer · 2022-05-13 11:53:52Z

0

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,'')

edited May 13, 2022 at 11:53

Thom A♦

97.6k12 gold badges67 silver badges102 bronze badges

answered Nov 8, 2018 at 11:08

Sarvesh Bandekar

968 bronze badges

1 Comment

Thom A Over a year ago

This doesn't return the result the OP wants if any of the values have the value '' or NULL.

Collectives™ on Stack Overflow

SQL Server concatenate ignore null value

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related