SQL string concatenation, separated by ',' only when column is not null

Question

I want update string (varchar) column in my database. I want to concatenate ',' + value.

This is working, but I don't want to add ',' (comma) if value in database column is empty.

It does not make sense to have comma at beginning.

UPDATE ImportState
SET ClientValidationRemark = ClientValidationRemark + ',' + @ClientValidationRemark
WHERE Id=@ImportId

Of course I want add comma every time if value is not empty. Do you know any simple, clean way to do this? I always update one row, @ImportId is the primary key.

Answer 1

Well, let me start by saying that keeping delimited string in a single column is a terrible database design, with only one exception: when the data is never used in sql and is needed as a delimited string in the application. In over 16 years of programming, I've only one time stored values like this, and it was exactly this scenario.

However, should you choose to store delimited values, here is something easy to do:

UPDATE ImportState
SET ClientValidationRemark = ISNULL(NULLIF(ClientValidationRemark, '') + ',', '') +
                             @ClientValidationRemark
WHERE Id=@ImportId

Taking advantage of the fact that concatenating strings to null values will result as null (Note: only when you use the string concatenation operator (+), Not when you use the built in function concat), I've used NULLIF to convert empty string values in ClientValidationRemark to null, and then ISNULL to convert the null + ', ' back to an empty string.