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 < 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?

Mammary

before you do anything make sure you backup the database as well. You would also use update to update any field. – Voodoo

possible duplicate of MySql - Way to update portion of a string? – Gastrocnemius

Possible duplicate of MySQL search and replace some text in a field – Cider

Answer 1

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

Complexioned

minor quibble: GREATERTHAN is '>' – Renaissance

Answer 2

UPDATE some_table SET some_field = REPLACE(some_field, '&lt;', '<')

Shirashirah

Fixed the < s. You need to use the encoding of &, like so: & :) – Rightist

I re-edited it. Thanks for letting me know. It dawned on me moments after I said I didn't know how. Thats the way my world works. :) – Shirashirah

thank you! this worked perfectly update a domain url as a substring replacement in a huge blog database! – Polyandrous

Answer 3

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

Lannylanolin

Answer 4

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

Piss

those null checks are redundant – Dwayne

Are there any easy and safe tools to create mysql stored procedure? – Skep

Answer 5

This will help you.

UPDATE play_school_data SET title= REPLACE(title, "'", "'") WHERE title = "Elmer's Parade";

Result:

title = Elmer's Parade

Greatuncle