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

Eugen Rieck · Accepted Answer · 2013-10-24 20:46:09Z

18

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.

edited Oct 24, 2013 at 20:46

answered Oct 24, 2013 at 20:25

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kevin Over a year ago

Please add some explanation.

Eugen Rieck Over a year ago

@Kevin I was quite sure, this is self-explaining! Anyway, I will add some explanation.

Benjamin Over a year ago

Great function and well explained!

Collectives™ on Stack Overflow

Replace part of string in mysql table column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related