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?

@Dominic Rodger What was wrong with the post that you edited it? Just want to know what I did wrong so as not to do it again. — Wes
– Wes, Commented Aug 13, 2010 at 12:56
nothing too serious (a couple of typos, and you needed backticks around <hr /> - it didn't show up in your original post). People around here tend to edit things to tidy them up - it doesn't necessarily reflect badly on you as the question asker/answerer - this site is collaboratively edited (see the FAQ: stackoverflow.com/faq) — Dominic Rodger
– Dominic Rodger, Commented Aug 13, 2010 at 13:08

Dominic Rodger · Accepted Answer · 2010-08-13 12:53:42Z

4

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.

answered Aug 13, 2010 at 12:53

Dominic Rodger

100k37 gold badges204 silver badges219 bronze badges

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

4 Comments

Wes Over a year ago

Okay thats useful but I fear the origional author just kept the button held down for whatever length they deemed appropiate. I may be able to use regex though to find those lengts and pass that into this. I'll give that a go.

Alexander Kjäll Over a year ago

if it's really important to use regex you can install it as a plugin in mysql, check here: mysqludf.org/lib_mysqludf_preg

Wes Over a year ago

Okay this sort of worked for me. However I'll have to do the work again, I'd love to see a cleaner solution

Wes Over a year ago

Accepting the answer for "MySQL does not have regular expression replace" which ultimately is correct and may help most people.

Collectives™ on Stack Overflow

Mysql replace regular expression

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related