Raskolnikov Raskolnikov - 4 months ago 7
SQL Question

SQL string concatenation, separate by ',' only when field is not null

I want update string(varchar) coulumn in DB. I want to concatenate ',' + value.
This is working, but I don't want to add ','(comma) if value in DB column is empty.
It does not make sense to have comma at beginning.

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


Off 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 primary key.

Answer

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, 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.

Comments