Replacing (permenantly) text within a string

[travisbrown]

I'm trying to permanently replace a certain word in all records of a table (SQL SERV 2000). I can of course use the REPLACE syntax; however, this only modifies the output. I think I also could change entire strings, but not text within a string. For example, I need to replace all instances of "

http://edmssappt1"

with "

http://edmssappp1"

that may be in a 16,000 character string.

The workaround I've used so far is export the table to Access and use the find/replace function. Doing this, however, is slow and makes me very uneasy.

Any help?

 

[tlbroadbent]

Use an update query SQL Server.

Update tblname
Set colname=replace(colname, '

http://edmssappt1',

'

http://edmssappp1')

-- Add criteria to only select rows where string occurs
Where charinsex('

http://edmssappt1',

colname)>0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[travisbrown]

Terry, is there a way to perform this on a text column? I get

"Argument data type text is invalid for argument 1 of replace function."

 

[travisbrown]

Sorry, nevermind. I remember you dealt with this in another post.