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

Thoughts how I might do this using WRITETEXT or UPDATETEXT?

Thanks,

Steve

Zamdrist (zamdrist (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jun 11, 5:52 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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...

On Jun 11, 10:00 pm, ZeldorBlat <zeldorb... (AT) gmail (DOT) com> wrote:
It appears my problem comes in when I try to use the + as a
concactenation operator while trying to update a TEXT filed. It
interprets it as an addition operator (lame).

The answer was to create a table using Select Into containing the new
value of the column I wanted, and to then do a straight up update
column to column, i.e.

Select mmatter, mdesc1 + ' (Stylized)' As mdesc
Into CWStylized from matter
Where (mmatter Like '10799.0062%'
Or mmatter Like '10799.0063%'
Or mmatter Like '10799.0061%')
And Right(mmatter, 2) Not Like 'T[aA-zZ]'
And Right(mmatter, 2) Not Like 'A[aA-zZ]'
And mdesc1 Not Like '%(Stylized)%'

Begin Transaction
Update matter Set mdesc1 = CWStylized.mdesc
Commit Transaction

Zamdrist (zamdrist (AT) gmail (DOT) com) writes:
I would expect fail for the same reason that the UPDATE failed. I ran:

CREATE TABLE textie (id int NOT NULL,
thetext text NULL)
go
INSERT textie(id, thetext)
VALUES (1, replicate('This is the original text', 2000))
go
SELECT id, thetext + '(Stylized)' AS ggg INTO newtextie FROM textie
go
SELECT datalength(thetext) FROM textie
SELECT datalength(thetext) FROM newtextie
go
DROP TABLE textie, newtextie

And I got:

(1 row(s) affected)
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the add operator.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx