Mysql replace regular expression

Hi is there any way to 'replace' all occurences of 10 or more occurances of _ (underscore) within a mysql table with something else.

background I've inherited a database of content written for the web.

Unfortunately the origional author has used ________________ instead of <hr /> for the horizontal rules.

It's a wordpress installation so the content is in a table called wp_posts.

I'm able to find the posts involved with the following query.

SELECT *
FROM `wp_posts`
WHERE `post_content` LIKE '%\_\_\_\_\_\_\_\_\_\_%'

Update :

I can find the posts that match using the following also

SELECT `post_content`
REGEXP '_{10,}'
FROM `wp_posts`
WHERE `post_content` LIKE '%\_\_\_\_\_\_\_\_\_\_%'
LIMIT 0 , 30

However that only returns 1 if theres a match not a 0 if there isn't.

Is there any way I can return the matching s开发者_如何学Pythonubstring? Is there a way to make the expression greedy?

MySQL does not have regular expression replace, but you can do what you want without it.

You can use REPLACE to replace an exact string:

update `wp_posts` set `post_content` = replace(`post_content`,'__________','<hr />');

Since this is a one-off job, I'd start by finding the highest count of _s used to represent an <hr /> - if that is 15, I'd first run that SQL command with a string with 15 _s, then with 14, then with 13, then with..., then with 10.