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
da******@gunner y.org

[Keith Kratochvil]

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

SELECT REPLACE(MediaNa me, 'myServer.gunne ry', 'newserver.gunn ery') 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(MediaNa me, 'myServer.gunne ry', 'newserver.gunn ery')
--SELECT @@rowcount AS RowsUpdated

--
Keith

"bj daniels" <da******@gunne ry.org> wrote in message news:eN******** ******@TK2MSFTN GP12.phx.gbl...

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
da******@gunner y.org

[bj daniels]

Looks Perfect - thanks!
"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:eP******** ******@TK2MSFTN GP10.phx.gbl...

UPDATE table SET mediaName = REPLACE(mediaNa me, '//myServer', '//newServer') WHERE CHARINDEX('//myServer', mediaName) > 0
"bj daniels" <da******@gunne ry.org> wrote in message
news:eN******** ******@TK2MSFTN GP12.phx.gbl...

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
da******@gunner y.org