UPDATETEXT, WRITETEXT

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?

Thanks,

Steve

Re: UPDATETEXT, WRITETEXT

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: UPDATETEXT, WRITETEXT

On Jun 11, 5:52 pm, Erland Sommarskog 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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...

Re: UPDATETEXT, WRITETEXT

On Jun 11, 10:00 pm, ZeldorBlat 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...

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

Re: UPDATETEXT, WRITETEXT

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
>From CWStylized
Inner Join matter On matter.mmatter = CWStylized.mmatter
Commit Transaction

Re: UPDATETEXT, WRITETEXT

Zamdrist (zamdrist@gmail.com) writes:
> 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
> From CWStylized
> Inner Join matter On matter.mmatter = CWStylized.mmatter
> Commit Transaction

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@sommarskog.se

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