Posted by ZeldorBlat on 06/12/07 03:00

On Jun 11, 5:52 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Zamdrist (zamdr...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland, in my experience I've been able to use normal INSERT and
UPDATE statements on columns of type TEXT without any problem. Why do
you even need to bother with all the READTEXT, WRITETEXT, and
UPDATETEXT stuff anyway? Your insight is appreciated...

[Back to original message]