Use REPLACE function with like operator
I would like to use the REPLACE function to replace specific text in a field. I only need to replace that portion of identified text in the field and leave the remaining text before and after the replace intact. How do I go about achieving this? I assume the LIKE operator is needed in some fashion?
In the example below would like to change the path information where field LIKE %H:PATH1\PATH2\PATH3% to %Z:\ABC\PATH1%. There is text before and after this value.
BEFORE
<text>H:PATH1\PATH2\PATH3<
AFTER
<text>Z:\ABC\PATH1<text>
BEFORE
<text>H:PATH1\PATH2\PATH3<
AFTER
<text>Z:\ABC\PATH1<text>
you can use a charindex instead:
--update table set <field> = replace(<field>, 'h:PATH1\PATH2\PATH3', 'z:\abc\path1')
select <field> as old, replace(<field>, 'h:PATH1\PATH2\PATH3', 'z:\abc\path1') as new
from table
where charindex( 'h:PATH1\PATH2\PATH3',<field>) > 0
The field is the column name of your table . . . as we don't know your schema the answer needed to be generic.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.