do a replace in update statement

Question

Post reply

do a replace in update statement

Bruin

SSCrazy

Points: 2058
More actions
September 13, 2020 at 7:41 pm

#3788141

I have a varchar(130) field called exportpath. I want to replace the word devserver with prodserver where ever it finds that string
in the field. Can it be ran with a select first to see and verify the output before doing the update?
data:
\\devserver\exporta\filename.txt
\\netloc\exporta\devserver\filename.txt
After update:
\\prodserver\exporta\filename.txt
\\netloc\exporta\prodserver\filename.txt
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16158 More actions · Answer 1

easy - a select with a where with a update with a replace and a where clause.

what have you tried so far and what issues did you get ? this is basic sql.

Bruin SSCrazy Points: 2058 More actions · Answer 2

not sure why I thought it was diffcult

update tblset exportfilename = REPLACE(exportfilename , 'devserver', 'prodserver')

where exportfilename like (%devserver%)

Thanks sorry..

Bruin SSCrazy Points: 2058 More actions · Answer 3

update tbl

set exportfilename = REPLACE(exportfilename , 'devserver', 'prodserver')

where exportfilename like (%devserver%)

ratbak Ten Centuries Points: 1379 More actions · Answer 4

Thanks to TSQL's support of updates with from clauses, I frequently write new or ad-hoc updates in this format (at least in development) to make testing easy:

update tblset 
set 
-- select *,
     exportfilename = REPLACE(exportfilename , 'devserver', 'prodserver')
from tblset
where exportfilename like '%devserver%';

Just execute from select forward when you want to see the update. Execute the whole statement to actually update once effect is validated.