SQL search and replace advanced

Question

Usually when I search and replace I use this code:

UPDATE wp_posts SET post_content = REPLACE (post_content, 'old string', 'new string');

Now I'd like to replace <h1>Title</h1>with <table>...<h1>Title</h1>...</table>

Problem is of course that the text in between tags is different for all pages (it is a wordpress homepage).

Any ideas on how to solve this? Is it possible?

I would retrieve the data, search/replace using regex or an XML-parser and then update the tables accordingly. Most programming languages have better tools for text editing than SQL in my opinion. How do you like that approach? — Morten Jensen, Oct 04 '12 at 09:42
Sounds reasonable. Do you mean retrieve all data to a text file and then upload text? Is this possible thru phpMyAdmin? — Emil Gunnarsson, Oct 04 '12 at 12:14
Yes you can use phpMyAdmin to export all fields to SQL or CSV or whatever. Then you can modify it and import again. You just want to do this once, right? — Morten Jensen, Oct 04 '12 at 12:20

score 1 · Answer 1 · answered Oct 04 '12 at 09:47

1

try this its update your column and add text to all of your record

UPDATE wp_posts SET post_content = `<table>`+ post_content+`</table>` ;

answered Oct 04 '12 at 09:47

Anant Dabhi

10,864
3
31
49

Sorry, maybe I wasn't perfectly clear. There is a lot more content in each page that should be outside the table-tags. Your solution would put in the beginning and
in the end right? – Emil Gunnarsson Oct 04 '12 at 11:50
Yes, this would wrap a `table`-tag around `post_content` :) – Morten Jensen Oct 04 '12 at 16:11

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

If all you need is to put the <table>...</table> tags at the beginning and end, then use Anant Dabhi's solution. However, if you need to do anything smarter than that, SQL is really not the right tool for the job.

The problems with simplistic processing of HTML are many:

What if the tag you are looking for exists within a comment?
What if the <h1> tag has attributes inside it?
What if there is more than one <h1>...</h1> within a post?

The potential problems go on and on.

Since you are using WordPress, why not use PHP, which gives you access to real HTML parsers? This discussion can get you started:

How do you parse and process HTML/XML in PHP?

edited May 23 '17 at 11:48

Community

1
1

answered Oct 04 '12 at 10:39

dan1111

6,576
2
18
29

I understand your 1-3 issues but I designed the webpage so I know that won't be a problem :) I was afraid SQL wasn't "advanced" enough...I guess some php solution could work. So I would design some php-script that I run once? – Emil Gunnarsson Oct 04 '12 at 11:52
@EmilGunnarsson, yes, that would work if you just want to apply it once to all existing posts. – dan1111 Oct 04 '12 at 13:11

score 0 · Answer 3 · answered Oct 04 '12 at 09:50

0

this two update statement can help you to do

UPDATE wp_posts 
 SET post_content = REPLACE (post_content, '<h1>', '<table><h1>');

 UPDATE wp_posts 
 SET post_content = REPLACE (post_content, '</h1>', '</table></h1>');

answered Oct 04 '12 at 09:50

solaimuruganv

27,177
1
18
23

This is not a good idea. It would break in many cases (commented out header, header that contains attributes, etc.). Also, you have the closing table tag coming before the closing h1 tag. – dan1111 Oct 04 '12 at 10:39

SQL search and replace advanced

3 Answers3