SQLTeam.com | Weblogs | Forums

Updating file name path


#1

Need to replace the servername in a column called Filename. i.e. \Servername\file name\ xxx.tif
There are approx. 49000 rows. Anyone help with a script for this. Thank you in advance


#2

First make a copy of the data.

This is one way to do this. It may not be the best.

Next if there are always two \ then look for the second one (CHARINDEX)
Use SUBSTRING to get the remainder of the text and add that to your new name


#3

Or STUFF the servername and append that to the new servername

No idea which is better, but it always T's me off that you have to provide a LENGTH to SUBSTRING as there is no "everything after that point" default option.


#4

Thanks folks, bit new to doing this stuff so could do with an easy method as possible


#5
DECLARE	@MyData	varchar(100) = '\Servername\file name\xxx.tif'

SELECT	'\MyNewServername\'
	+ STUFF(@MyData
		, 1, CHARINDEX('\', @MyData, 2)	, '')

	

#6

Thanks folks, got something to work now. Much appreciated