Posted By: Anonymous
We have a database that has a bunch of records with some bad data in one column, in which an embedded editor escaped some stuff that shouldn’t have been escaped and it’s breaking generated links.
I want to run a query to replace the bad characters in all the records, but can’t figure out how to do it. I found the
replace() function in MySQL, but how can I use it inside a query?
For example, what would be the correct syntax if I wanted to replace the string
< with an actual less-than angle bracket (
<) in all records that have
< in the
articleItem column? Can it be done in a single query (i.e. select and replace all in one swoop), or do I have to do multiple queries? Even if it’s multiple queries, how do I use
replace() to do the replace on the value of a field on more than one record?
At a very generic level
UPDATE MyTable SET StringColumn = REPLACE (StringColumn, 'SearchForThis', 'ReplaceWithThis') WHERE SomeOtherColumn LIKE '%PATTERN%'
In your case you say these were escaped but since you don’t specify how they were escaped, let’s say they were escaped to
UPDATE MyTable SET StringColumn = REPLACE (StringColumn, 'GREATERTHAN', '>') WHERE articleItem LIKE '%GREATERTHAN%'
Since your query is actually going to be working inside the string, your
WHERE clause doing its pattern matching is unlikely to improve any performance – it is actually going to generate more work for the server. Unless you have another WHERE clause member that is going to make this query perform better, you can simply do an update like this:
UPDATE MyTable SET StringColumn = REPLACE (StringColumn, 'GREATERTHAN', '>')
You can also nest multiple
UPDATE MyTable SET StringColumn = REPLACE (REPLACE (StringColumn, 'GREATERTHAN', '>'), 'LESSTHAN', '<')
You can also do this when you select the data (as opposed to when you save it).
So instead of :
SELECT MyURLString From MyTable
You could do
SELECT REPLACE (MyURLString, 'GREATERTHAN', '>') as MyURLString From MyTable