I need to programatically append some string to col1. The below code works only in case col1 is not empty. If it's empty then after running the code it remains empty. Why?
UPDATE
table
SET
col1 = col1 + ';somestring'
WHERE
col2 = rowID
That's because any operation with NULL results in NULL. You need to use ISNULL() to "transform" your NULL values into empty strings:
UPDATE
table
SET
col1 = ISNULL(col1, '') + ';somestring'
WHERE
col2 = rowID
You have two options to solve this.
First use ISNULL
DECLARE @var nvarchar(10) -- not initialized (null)
SELECT @var + N'test' -- yields null
-- use ISNULL to fix it
SELECT ISNULL(@var,N'') + N'test'
GO
Second disabling the NULL_YIELDS_NULL if you have many of this operations.
DECLARE @var nvarchar(10) -- not initialized (null)
-- you can also disactivate this behaviour for this session
-- This way all null concats will be interpreted as an empty string
SET CONCAT_NULL_YIELDS_NULL OFF --disable null yields null for one ore more operations
SELECT @var + N'test'
SET CONCAT_NULL_YIELDS_NULL ON --reenable it, if you don't need it disabled anymore
GO
