SQL Server - Adding a string to a text column (concat equivalent)

Question

How do you add a string to a column in SQL Server?

UPDATE [myTable] SET [myText]=' '+[myText] 

That doesn't work:

The data types varchar and text are incompatible in the add operator.

You would use concat on MySQL, but how do you do it on SQL Server?

Answer 1

like said before best would be to set datatype of the column to nvarchar(max), but if that's not possible you can do the following using cast or convert:

-- create a test table  create table test (     a text )  -- insert test value insert into test (a) values ('this is a text') -- the following does not work !!! update test set a = a + ' and a new text added' -- but this way it works:  update test set a = cast ( a as nvarchar(max))  + cast (' and a new text added' as nvarchar(max) ) -- test result select * from test -- column a contains: this is a text and a new text added 

hope that helps

Answer 2

Stop using the TEXT data type in SQL Server!

It's been deprecated since the 2005 version. Use VARCHAR(MAX) instead, if you need more than 8000 characters.

The TEXT data type doesn't support the normal string functions, while VARCHAR(MAX) does - your statement would work just fine, if you'd be using just VARCHAR types.