using a sql statement to do a string replace

[bj daniels]

I am not certain which group to post this to - sorry if this is the wrong
group:

I have a table that holds the location of various media and such (mostly
images). One field is the Media name, the other is the URL in the form
http://myServer.gunnery.org/media/pic1 for example. we are about to move
all the media to a new location (new server). the paths will remain the
same except for the server name.

Is there an update statement that will let me go through the table an
replace myServer with newServer in all the URLs (and leave the rest of the
URL alone). So the link above would become
http://newServer.gunnery.org/media/pic1. I know how I would do it with VB,
but I am not as familar with all the tricks in SQL.

thanks

bj daniels
(e-mail address removed)

 

[Keith Kratochvil]

You should be able to use the REPLACE function. This select statement should show you that it works as expected:

SELECT REPLACE(MediaName, 'myServer.gunnery', 'newserver.gunnery') FROM YourTable


After verifying that the select gives you the expected result, run the following update to update the data within the table:

--UPDATE YourTable SET MediaName = REPLACE(MediaName, 'myServer.gunnery', 'newserver.gunnery')
--SELECT @@rowcount AS RowsUpdated

 

[bj daniels]

Looks Perfect - thanks!

UPDATE table SET mediaName = REPLACE(mediaName, '//myServer', '//newServer')
WHERE CHARINDEX('//myServer', mediaName) > 0