How can I use mySQL replace to replace strings in multiple records

Question

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  amp lt  with an actual less-than angle bracket   lt   in all records that have  amp lt  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

User · Answer

This will help you   UPDATE play school data SET title  REPLACE title    amp  39         WHERE title    Elmer amp  39 s Parade     Result   title   Elmer s Parade

User · Answer

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 GREATERTHAN  UPDATE MyTable  SET StringColumn   REPLACE  StringColumn   GREATERTHAN     gt     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     gt      You can also nest multiple REPLACE calls  UPDATE MyTable SET StringColumn   REPLACE  REPLACE  StringColumn   GREATERTHAN     gt      LESSTHAN     lt      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     gt    as MyURLString From MyTable

User · Answer

UPDATE some table SET some field   REPLACE some field    amp lt      lt

User · Answer

you can write a stored procedure like this   CREATE PROCEDURE sanitize TABLE   BEGIN  replace space with underscore UPDATE Table SET FieldName   REPLACE FieldName  quot   quot   quot   quot   WHERE FieldName is not NULL   delete dot UPDATE Table SET FieldName   REPLACE FieldName  quot   quot   quot  quot   WHERE FieldName is not NULL   delete   UPDATE Table SET FieldName   REPLACE FieldName  quot   quot   quot  quot   WHERE FieldName is not NULL   delete   UPDATE Table SET FieldName   REPLACE FieldName  quot   quot   quot  quot   WHERE FieldName is not NULL   raplace or delete any char you want                             END  In this way you have modularized control over table  You can also generalize stored procedure making it  parametric with table to sanitoze input parameter

User · Answer

Check this  UPDATE some table SET some field   REPLACE  Column Name String    Search String    Replace String     Eg with sample string   UPDATE some table SET some field   REPLACE  this is test string    test    sample     EG with Column Field Name   UPDATE some table SET some field   REPLACE columnName   test    sample

[mysql] How can I use mySQL replace() to replace strings in multiple records?

Examples related to mysql

Examples related to replace