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.
SET ClientValidationRemark = ClientValidationRemark + ',' + @ClientValidationRemark
WHERE [email protected]
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 [email protected]
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.