Query and update on Text Field

[teamssc]

I'm having a tough time with running a update on a text field and would appreciate anybodies help.

SQL Server 5.0

Thanks

>>>>>>>>>


UPDATE GM2.DBO.ENVIRONMENT
SET (convert(varchar(16),val))='baaaaaaafaaaaaaaaaaaaaaaaaaaaaaacgcaaaaaaaaaaaaahbaaaaaaaaaaaaaaaaaaaaaahbaaaaaacgcaaaaabaaaaaaabaaaaaaaofbaaaaaajbaaaaaeidaaaaamlcaaaaaaa'
WHERE (convert(varchar(16),val))='baaaaaaaeaaaaaaaaaaaaaaaaaaaaaaanadaaaaacfacnakaioaaaaaaaaaaaaaaaaaaaaaaioaaaaaanadaaaaabaaaaaaaaaaaaaaamcbaaaaaagcaaaaacfdaaaaamidaaaaaaa'

 

[RyanEK]

Hi,

Use UpdateText eg.

http://www.tek-tips.com/viewthread.cfm?qid=1484373

Ryan

 

[teamssc]

thank you for the VERY QUICK reply (3am my time).

I'm lost as the update query you reference is very long and I'm not sure of all the syntax I need to rewrite it. Do I have to do all of the declares? Can you help?

Thanks

........................................

declare @otxt varchar(1000)
set @otxt = 'old text'
declare @ntxt varchar(1000)
set @ntxt = 'new text'

declare @txtlen int
set @txtlen = len(@otxt)
declare @ptr binary(16)
declare @pos int
declare @id int
declare curs cursor local fast_forward
for select @id, textptr([field]), charindex(@otxt, [field])-1
from

where [field] like '%' + @otxt +'%'
open curs
fetch next from curs into @id, @ptr, @pos
while @@fetch_status = 0
begin
updatetext

.[field] @ptr @pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end
close curs
deallocate curs

 

[teamssc]

If anyone could help explain the proposed solution or another solution I could use the help.

Also I had a type in the original post as SQL Server 5.0 and that sould have been 2005.

 

[mrdenny]

Yes you will need most of those declares. Those are used to find the position of the text value within the column, and then update the value with the new value. Look up UPDATETEXT in Books Online, it's got a bunch on info in there as well.

What was the original error that you were getting? I assume it was related to using a text column in a where clause?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog