How to append to a text field in t-sql SQL Server 2005

Question

What is the best way to append to a text field using t-sql in Sql Server 2005?

With a varchar I would do this.

update tablename set fieldname = fieldname + 'appended string'

But this doesn't work with a text field.

Off the top of my head I would say that should work. The issue is probably a conversion/casting issue. Try fieldname = fieldname + N'appended string' — Craig, Jan 21 '09 at 17:57
Unfortunately, that didn't work. I got 'The data types text and nvarchar are incompatible in the add operator.' Thanks though. — Paul D. Eden, Jan 21 '09 at 18:03

score 40 · Accepted Answer · edited Jan 21 '09 at 18:42

40

Try this:

update 
  tablename
set
  fieldname = convert(nvarchar(max),fieldname) + 'appended string'

edited Jan 21 '09 at 18:42

casperOne

answered Jan 21 '09 at 18:01

Bravax

1

Well, at least I knew it was some sort of conversion error. :) Thanks Bravax. – Craig Jan 21 '09 at 18:27
3

If fieldname is null this won't work. instead of fieldname try COALESCE(fieldname, '') – nuander Jun 14 '13 at 19:49

Joe · Answer 2 · 2012-10-24T20:58:22.567

6

Copied from link:

DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(ntextThing)
FROM item
WHERE id =1
UPDATETEXT table.ntextthing @ptrval NULL 0 '!'
GO

edited Oct 24 '12 at 20:58

answered Jan 21 '09 at 17:59

Joe

score 3 · Answer 3 · answered Jan 21 '09 at 18:01

3

in 2005 you should use varchar(max) or nvarchar(max) these columns will work with normal varchar functions. Text and ntext have been deprecated

answered Jan 21 '09 at 18:01

SQLMenace

score 0 · Answer 4 · edited Dec 04 '19 at 05:22

0

The max length for varchar(max) is 2,147,483,647 characters. This is the same as the Text data type.

Whatever text could hold, this can hold, so you don't need to worry about running out of room by switching to VARCHAR(MAX).

edited Dec 04 '19 at 05:22

CharithJ

answered Jul 15 '11 at 18:37

Todd

4 Answers4