Replace part of string in mysql table column

Question

I have a wordpress installation and I've broken the mysql database. For posts the urls are listed as '.../wordpress//...' instead of '.../wordpress/...'

How in SQL can I go through every row in the table and (perhaps use a regular expression) to replace every instance of 'ss//' with 'ss/'?

Answer 1

UPDATE sometable SET somefield=REPLACE(somefield,'/wordpress//','/wordpress/');

Edit

@Kevin asked me to explain this query, so here we go:

• I assume the basic UPDATE is clear: In all rows of sometable assign a new value to somefield
• the REPLACE() function does exactly what it says: It replaces text. In our use case we ant it to take the old value of somefield, then replace all ocurrencies of '/wordpress//' with '/wordpress/'
• these two parts taken together mean, in all rows of sometable assign somefield the value, that results, if you replace all ocurrencies of '/wordpress//' with '/wordpress/' in the old value.