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

hmm, try doing CAST(' ' AS TEXT) + [myText]

Although, i am not completely sure how this will pan out.

I also suggest against using the Text datatype, use varchar instead.

If that doesn't work, try ' ' + CAST ([myText] AS VARCHAR(255))

Answer 2

The + (String Concatenation) does not work on SQL Server for the image, ntext, or text data types.

In fact, image, ntext, and text are all deprecated.

ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

That said if you are using an older version of SQL Server than you want to use UPDATETEXT to perform your concatenation. Which Colin Stasiuk gives a good example of in his blog post String Concatenation on a text column (SQL 2000 vs SQL 2005+).

Answer 3

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.

Answer 4

Hi, 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