I want to update a string in a table-CodePudding

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

CodePudding user response：

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')

CodePudding user response：

you need to split value to avoid an incorrect update

update CH
set CH.doc_filename = 'new_path'   '|'   P.right_part
from content_history CH
    outer apply
    (
        select left(CH.doc_filename, charindex('|', CH.doc_filename) - 1) as left_part
            ,right(CH.doc_filename, len(CH.doc_filename) - charindex('|', CH.doc_filename)) as right_part
        where charindex('|', CH.doc_filename) != 0
    ) P
where P.left_part = 'old_path'