How can I use mySQL replace() to replace strings in multiple records?
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', '>')
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 REPLACE
calls
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
UPDATE some_table SET some_field = REPLACE(some_field, '<', '<')
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')
you can write a stored procedure like this:
CREATE PROCEDURE sanitize_TABLE()
BEGIN
#replace space with underscore
UPDATE Table SET FieldName = REPLACE(FieldName," ","_") WHERE FieldName is not NULL;
#delete dot
UPDATE Table SET FieldName = REPLACE(FieldName,".","") WHERE FieldName is not NULL;
#delete (
UPDATE Table SET FieldName = REPLACE(FieldName,"(","") WHERE FieldName is not NULL;
#delete )
UPDATE Table SET FieldName = REPLACE(FieldName,")","") 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
This will help you.
UPDATE play_school_data SET title= REPLACE(title, "'", "'") WHERE title = "Elmer's Parade";
Result:
title = Elmer's Parade