I have a table, content_history with a column, doc_filename, and each row has a string value. Each string has a | and the portion in front of the | is a filepath. I want to replace that portion of the string with the correct filepath if it doesn’t already match. What is the best way to go about doing this? Currently I use:
UPDATE content_history SET doc_filename = replace (doc_filename, 'path that needs to be replaced', 'new path') WHERE doc_filename LIKE 'old path%'
But if I don’t have the exact path it doesn’t replace so I have to run a select * query and manually go through and input all the different paths that are incorrect. It’s not a viable long-term solution
Advertisement
Answer
Ideally you wouldn’t store multiple values as delimited values in a single value, you should have a separate column for each distinct value, then you wouldn’t be asking such a question.
You can use stuff
:
set doc_filename=Stuff(doc_filename, 1, CharIndex('|', doc_filename)-1, 'new path')