CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlappend
Paul D. Eden
Paul D. Eden

Reputation: 20629

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

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.

Upvotes: 23

Views: 55181

Answers (4)

Todd
Todd

Reputation: 439

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

Upvotes: 0

Joe
Joe

Reputation: 42666

This should work (link)

Copied from link:

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

Upvotes: 6

Bravax
Bravax

Reputation: 10493

Try this: 

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

Upvotes: 40

SQLMenace
SQLMenace

Reputation: 135171

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

Upvotes: 3

Related Questions