Hey all,

I was just wondering if anyone could help me out with a SQL Query.

I recently upgraded the forum to use a light background and as you could imagine the cyan and white text that was used on the old darker background is now pretty hard to read.

I need to run a query that will replace all instances of <FONT color=cyan> with <FONT color=black> in tblthread.message.

I'm just learning SQL so I'm not familiar with what to use to achieve this.

Any help would be greatly appreciated.

Thanks in Advance

Matt

Thanks.

Do you mean Download that column into Access and use the Find and Replace function and then Reupload with DTS?

I'm willing to learn about the updatetext function so any examples you could give me to do this would be appreciated.

I know a lot about the Server Admin side of SQL but not enough about the programming side yet.

Hey,

Thanks for the info.

I have tried this with access connecting to the database with an ODBC and Access keeps locking up when I hit replace all.

How do I use the Updatetext function with a cursor?

something like this maybe

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_6i2c.asp - http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/tsqlref/ts_ua-uz_6i2c.asp

Can't work out Microsofts Example though.

Basically what I want to achieve is replace 'text1' with 'text2' in tblthread.message.

Just don't know what to put where in this example:

USE pubs
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info pr, publishers p
      WHERE p.pub_id = pr.pub_id
      AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

Well, the example of microsoft is fairly simple stated, but for your example something like this should work:

WHILE (Select Count(Thread_ID) from tblThread where message LIKE '%<FONT color=cyan>%') > 0
Begin
Declare @textptr binary(16), @patindex int, @patlength int
Select  @textptr=Textptr(message), 
 @patindex=Patindex('%<FONT color=cyan>%',message)-1, 
 @patlength=Datalength('<FONT color=cyan>')
from tblThread (UPDLOCK)
WHERE PATINDEX('%<FONT color=cyan>%',message)<>0
UpdateText tblThread.Message @textptr @patindex @patlength '<FONT color=black>'
END

Thanks for this Micheal.

Sorry about the late reply.

It worked well apart from hosing the CPU on the server. Lucky I ran it on a Non Production Server.

I see now why you said that creating a Textptr was to be avoided if possible.

Anyway Thanks again.

Matt