Aximili Aximili - 1 month ago 13
SQL Question

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

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

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