37

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?

Aximili
  • 28,626
  • 56
  • 157
  • 216

6 Answers6

67

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
starball
  • 20,030
  • 7
  • 43
  • 238
Tobias Pirzer
  • 1,005
  • 10
  • 13
17

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • VARCHAR(MAX) is recommendable only when you need 8000 chars or less... For more than 8000 chars you have no choice but use it. – m0rg4n Sep 06 '19 at 14:58
  • 1
    @m0rg4n: sorry, but this is utter BS - `VARCHAR(MAX)` supports **up to 2 GB** of data - not just 8000 chars. `TEXT` is **not supported** - don't use it. Period. – marc_s Sep 06 '19 at 14:59
9

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+).

ahsteele
  • 26,243
  • 28
  • 134
  • 248
9
UPDATE test SET a = CONCAT(a, "more text")
Floern
  • 33,559
  • 24
  • 104
  • 119
koesys
  • 123
  • 2
  • 7
5

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))

Meiscooldude
  • 3,671
  • 5
  • 27
  • 30
  • Thanks but the first one gives this error Operand data type text is invalid for add operator. myText is of type text, not varchar and I don't know how long the text can be, it could be very long – Aximili Jun 23 '10 at 06:12
  • hmm, I suppose you 'COULD' use VARCHAR(MAX) instead of VARCHAR(255), but it seems there is probably a better way. – Meiscooldude Jun 23 '10 at 06:18
0

To Join two string in SQL Query use function CONCAT(Express1,Express2,...)

Like....

SELECT CODE, CONCAT(Rtrim(FName), " " , TRrim(LName)) as Title FROM MyTable
Muhammad Aamir Jahangir
  • 1