CODEWITHSUNDEEP
sql-serverstringstring-concatenation
Eclipse
Eclipse

Reputation: 309

SQL Server concatenation separated by comma only when column is not null

I have the following SQL statement:

@All = COALESCE(NULLIF(@Asc1, '') + ',', '') +
        OALESCE(NULLIF(@Asc2, '') + ',', '') +
        OALESCE(NULLIF(@Asc3, '') + ',', '');

This will insert a comma at the end even if any of the variables (@Asc1, @Asc2, @Asc3) have NULL or empty values.

For example:

I would like @All to be 1234,3456

Thanks.

Upvotes: 10

Views: 8997

Answers (2)

Shammas
Shammas

Reputation: 461

I know this post is already answered and is correct. But would like to post the below answer because from SQL Server 2017 onwards, this is too easy and someoen might find this helpful in future.

SELECT @all = CONCAT_WS(',',@Asc1,@Asc2,@Asc3)

Upvotes: 9

SqlZim
SqlZim

Reputation: 38043

using stuff() to remove the first comma and reversing the comma concatenation:

set @all = stuff(
    coalesce(','+nullif(@Asc1, ''), '')
  + coalesce(','+nullif(@Asc2, ''), '')
  + coalesce(','+nullif(@Asc3, ''), '')
  ,1,1,'');

rextester demo: http://rextester.com/UNDS90887

returns: 1234,3456

Upvotes: 9

Related Questions