CODEWITHSUNDEEP
sql-servert-sql
yesman
yesman

Reputation: 7838

Replace last part of a string

I have several pathnames like this: 

some\cool\path\1\d32a
some\cool\path\2\x3rydf
some\cool\path\3\y42d
some\cool\path\4\qasdf3
some\cool\path\5\jv5

I want to replace the last part of the path with \newPath on the paths that have 1, 2 or 3 in them (not 4 or 5). Expected result would be: 

some\cool\path\1\newPath
some\cool\path\2\newPath
some\cool\path\3\newPath
some\cool\path\4\qasdf3
some\cool\path\5\jv5

I could do something like this: 

UPDATE mytable
SET Viewsmap = [method I'm looking for]
WHERE PathName like '%1%'
OR PathName like '%2%'
OR PathName like '%3%'

But I have no idea how to change the last part of each string, as it's unknown what the last part of the path will be, and the Replace function doesn't seem to accept wildcards.

Upvotes: 0

Views: 2351

Answers (2)

shA.t
shA.t

Reputation: 16968

I think you can use PATINDEX like this:

select 
    case when patindex('%\[123]\%',p) > 0 then 
        substring(p,1,PATINDEX('%\[123]\%',p) + 2) + 'newpath' 
    else p 
    end newP
from t;

And in your UPDATE command:

update mytable
set Viewsmap = substring(PathName, 1, PATINDEX('%\[123]\%',PathName) + 2) + 'newpath'
where patindex('%\[123]\%', PathName) > 0;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use:

LEFT (Viewsmap, LEN(Viewsmap) - CHARINDEX('\', REVERSE(Viewsmap)) + 1 )

in order to extract the part that is before the last occurrence of \.

You can then easily UPDATEusing:

UPDATE mytable
SET Viewsmap = CONCAT(LEFT(Viewsmap, LEN(Viewsmap) - CHARINDEX('\', REVERSE(Viewsmap)) + 1 ),
                      'newPath')
WHERE PathName like '%1%'
      OR PathName like '%2%'
      OR PathName like '%3%'

Upvotes: 1

Related Questions