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.
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:
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 functionconcat
), I've usedNULLIF
to convert empty string values inClientValidationRemark
to null, and thenISNULL
to convert thenull + ', '
back to an empty string.