Skip to content
Advertisement

I want to update a string in a table

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')
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement