| Author |
 Topic Search  Topic Options
|
Matt270581 
Newbie
Joined: 06 August 2003
Location: Australia
Status: Offline
Points: 13
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: SQL Query?
Posted: 09 September 2003 at 11:19am |
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
|
 |
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 September 2003 at 1:23pm |
|
As message is a text field, you cannot use the replace funtion, you would have to use the updatetext function. As this might be to advanced for a beginner you might be better off writing a little asp page to use vbs to do that. Plus in case you use Access the script would not work, but access has a built in query to do that, search the access help for replace in case you use access.
|
|
|
|
Matt270581
Newbie
Joined: 06 August 2003
Location: Australia
Status: Offline
Points: 13
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 September 2003 at 9:36pm |
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.
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 September 2003 at 7:14am |
|
The Updatetext function is poweful thus in your case you would have to run it together with a cursor which is advisable to avoid if possible. What you can do, link to the SQL db's in access (File>GetExternal>Data>LinkTables) and use Find and replace this way. Probably the quickest way.
|
|
|
|
Matt270581
Newbie
Joined: 06 August 2003
Location: Australia
Status: Offline
Points: 13
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 September 2003 at 9:48am |
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?
|
|
Matt270581
Newbie
Joined: 06 August 2003
Location: Australia
Status: Offline
Points: 13
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 September 2003 at 10:45am |
something like this maybe
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
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 September 2003 at 12:00pm |
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
|
|
|
|
|
Matt270581
Newbie
Joined: 06 August 2003
Location: Australia
Status: Offline
Points: 13
|
Post Options
Thanks(0)
Quote Reply
Posted: 02 October 2003 at 9:47am |
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
|
|
