SQL query to update all headlines in Wordpress database

Question

I like to replace all my custom headlines in my wordpress blog posts with regulat “h” headlines. For example:

[x_custom_headline type=“left“ level=“h2″ looks_like=“h3″]Here is the headline[/x_custom_headline]

should replaced to

<h2>Here is the headline</h2>

and

[x_custom_headline type=“left“ level=“h3″ looks_like=“h7″]Here is another headline[/x_custom_headline]

should replaced by

<h3>Here is another headline</h3>

I have more than 1000 posts, so doing this manually would take weeks or month...

T tried something like:

UPDATE wp_posts
    SET post_content = REPLACE(
      REPLACE(post_content, '[x_custom_headline type="left" level="h2" looks_like="h3"]', '<h2>'),
      '[/x_custom_headline]','</h2>')
WHERE post_content LIKE '[x_custom_headline type="left" level="h2" looks_like="h3"]%[/x_custom_headline]';

...but the WHERE condition does not work in this query.

Can anybody maybe help me to find the right SQL query (maybe regex?) to update this directly in the database?

This is a typical post structure:

Post Content
[x_custom_headline type="left" level="h2" looks_like="h3"]This is a h2 headline[/x_custom_headline]
Post Content
[gallery ids="92149,92151,92153,92155"]
More Post Content
[x_custom_headline type="left" level="h3" looks_like="h7"]This is a h3 headline[/x_custom_headline]
More Post Content
[x_custom_headline type="left" level="h3" looks_like="h7"]This is a h3 headline[/x_custom_headline]
More Post Content
<img src="image-url" class="skip-lazy"/>
[x_custom_headline type="left" level="h2" looks_like="h3"]This is a h2 headline[/x_custom_headline]
More Post Content
[sc name="reiseberichte-seychellen"]
More Post Content

Michael

What version of mysql are you using? If you're not sure run the query `select version();` — , Mar 07 '22 at 09:43
on my staging 10.3.32-MariaDB-log-cll-lve, on my live system I have version 8.0.25-15 — michaelxxx, Mar 07 '22 at 09:46

score 0 · Answer 1 · answered Mar 10 '22 at 12:41

0

Here we have created a function which loops using WHILEuntil it no longer finds ="h[1-6] in the string.

CREATE FUNCTION h_tag_while
       (wp_post varchar(1000))
RETURNS varchar(1000)
DETERMINISTIC
BEGIN 
  DECLARE poste varchar(1000);
  DECLARE posH int;
  DECLARE posT int;
  DECLARE Hx char(2);
  SET poste = wp_post;
  SET Hx = right(regexp_substr(
      poste,'="h[1-6]'),2);
WHILE LEFT(Hx,1) = 'h' DO
  SET posT =  locate(
  '[/x_custom_headline]',poste);
  SET posH = locate(
   '[x_custom_headline type="left"',
   poste);
   SET poste = concat(
    substring(poste,1,posH-1),
    '<',Hx,'>',
    substring(poste,posH+58,posT-posH-58),
    '</',Hx,'>',
    substring(poste,posT+20));
   SET Hx = right(regexp_substr(
      poste,'=[″"]h[1-6]'),2);
 END WHILE;
RETURN coalesce(poste,wp_post);
END

we can see the output with the query

select 
  h_tag_while(post_content)
from wp_posts;

and when we are satisfied that all is well we can modify the data in the table with

update wp_posts
set post_content = h_tag_while(post_content);

See the following dbfiddle for testing and development. There is also the first function which only updates the first occurrence of ="h[1-6]. The other problem is that it returns null if the search does not succeed.

answered Mar 10 '22 at 12:41

Thank you for the update. I have never worked with CREATE FUNCTION before. Can I run this under the regular SQL tab in phpmyadmin? When I do this I get an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 Or do I have to add this function unser the tab "Routines"? – michaelxxx Mar 10 '22 at 15:28
Please see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=65177bca8fdf7d316b69565c5f075885 to test and let me know. I'll have another look this evening. – Mar 10 '22 at 15:35
It works perfectly on dbfiddle. But I can´t transfer this to my phpmyadmin :( – michaelxxx Mar 10 '22 at 18:42
Have a look at. https://stackoverflow.com/questions/14176583/create-a-function-with-phpmyadmin and https://stackoverflow.com/questions/17481890/how-to-write-a-stored-procedure-in-phpmyadmin and see whether you can make it work. Try with a simple function from a tutorial first? – Mar 10 '22 at 21:48
I really tried it. It was no problem to add a simple function to my phpmyadmin. But when I try to add your code I get SQL Syntax Errors: MySQL meldet: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$ CREATE FUNCTION h_tag_while (wp_post varchar(1000)) RETURNS varchar' at line 1 – michaelxxx Mar 11 '22 at 16:02
Personally I find that the $$ seperator causes more problems than it solves. That's why I haven't used it here . Did you try without it? You should be able to create a function in the SQL tab and in the routines tab so try both. – Mar 11 '22 at 16:35
Yes, I tried both and unfortunately it did not work. I do not know what the problem is. Does it still make sense to discuss this further here in the forum? I really thank you very much for your help. And I really need this solution!!! But I'm afraid about the forum we do not get further. Maybe you can contact me directly omp-blog(at)gmx.com? – michaelxxx Mar 11 '22 at 19:12

SQL query to update all headlines in Wordpress database

1 Answers1