SQL Replace only a part of a path

Question

I have a table (about 160k rows) with a column called paths. In that column there are paths like:

"\\ab.local\folder1\folder2\folder3\folder_x"

"\\ab.local\folderA\"

The length of the paths differ.

What I would like to do is to replace only the "ab" before the .local in to "cd" and leave the rest untouched.

I have been told to use a replace function, but somehow I don't get it to work the way I want to.

I am looking for the right syntax to do this.

Please show is your query which isn't working so we can help you correct it... — Dale K, Aug 08 '19 at 06:39
Also put different examples. Does it always start with \\? Is it always ab.? If not, is it always 2 chars and a dot? Etc. — EzLo, Aug 08 '19 at 06:40

Bill Tür stands with Ukraine · Answer 1 · 2019-08-08T08:17:07.027

0

If the part to replace is on a fixed position with a fixed length you could use STUFF, like so:

UPDATE yourTable SET paths = STUFF(paths, 3, 2, 'cd')

This replaces two characters in paths beginning at position 3 with cd

edited Aug 08 '19 at 08:17

answered Aug 08 '19 at 06:42

Bill Tür stands with Ukraine

3,425
30
38
48

score 0 · Accepted Answer · answered Aug 08 '19 at 06:50

0

Declare @oldval as varchar(30) = '\\ab.local\' ;
Declare @newval as varchar(30) = '\\cd.local\' ; 

update yourtable set yourfield  = replace(yourfield,@oldval ,@newval) where yourfield like @oldval + '%'

answered Aug 08 '19 at 06:50

Wilhelm

196
10

score 0 · Answer 3 · answered Aug 08 '19 at 08:11

Solved Reference : https://stackoverflow.com/a/814551/6923146

Hope it's perfect for your solution

UPDATE my_table
SET columnName = replace(columnName, 'oldstring', 'newstring')
WHERE columnName like '%oldstring%'

For example:

UPDATE my_table
SET columnName = replace(columnName, '\ab.', '\ab.')
WHERE columnName like '%\ab.%'

SQL Replace only a part of a path

3 Answers3