UPDATETEXT, WRITETEXT

[Zamdrist]

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.mmatt er = matters.matteri d

Thoughts how I might do this using WRITETEXT or UPDATETEXT?

Thanks,

Steve

[Erland Sommarskog]

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.mmatt er = matters.matteri d
>
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(thet ext)
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@sommarsk og.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

[ZeldorBlat]

Re: UPDATETEXT, WRITETEXT

On Jun 11, 5:52 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

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.mmatt er = matters.matteri d

>

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(thet ext)
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...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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...

[Zamdrist]

Re: UPDATETEXT, WRITETEXT

On Jun 11, 10:00 pm, ZeldorBlat <zeldorb...@gma il.comwrote:

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

[Zamdrist]

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.mdes c

>From CWStylized

Inner Join matter On matter.mmatter = CWStylized.mmat ter
Commit Transaction

[Erland Sommarskog]

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.mdes c
From CWStylized
Inner Join matter On matter.mmatter = CWStylized.mmat ter
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(thet ext) FROM textie
SELECT datalength(thet ext) 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@sommarsk og.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