Updating file name path

Transact-SQL
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)	, '')
1 Like
6

Thanks folks, got something to work now. Much appreciated