Mysql replace regular expression

Question

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 substring? Is there a way to make the expression greedy?

Answer 1

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.