Posted by Erland Sommarskog on 06/11/07 21:52

Zamdrist (zamdrist@gmail.com) writes:
> I need to update/change the text in a table column of data type TEXT.
> It's a smaller set of records (72) where I need to append onto the end
> a string of text, the same for all records.
>
> I don't quite understand how UPDATETEXT and WRITETEXT work, but here
> is how I would write the Update query were the field not a TEXT type.
>
> Update Matters Set Description = Description + ' (Stylized)'
> From Matters
> Inner Join cwconnect On cwconnect.mmatter = matters.matterid
>
> Thoughts how I might do this using WRITETEXT or UPDATETEXT?

Here is a quick example on using UPDATETEXT. Note that you need to
work with one row at a time.

The script retrieves a text pointer which is, well, let's call it a
technology of the eighties. No wonder that Microsoft has deprecated the
text data type in SQL 2005 in favour of varchar(MAX), which is a lot
easier to work with. Anyway, you need this text point when you work
with READTEXT, WRITETEXT and UPDATETEXT. You also need to know the
current length of the string, to tell where to add the new text. The
parameter that is NULL, is a delete-length where you could specify
how many characters that are to be deleted.

CREATE TABLE textie (id int NOT NULL,
thetext text NULL)
go
INSERT textie(id, thetext)
VALUES (1, 'This is the original text')
go
DECLARE @ptr varbinary(16),
@offset int
SELECT @ptr = textptr(thetext), @offset = datalength(thetext)
FROM textie
WHERE id = 1

UPDATETEXT textie.thetext @ptr @offset NULL ' (Stylized)'
go
SELECT * FROM textie
go
DROP TABLE textie




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]