2

I want delete h1 tags and text between them from post_content sql column.

I have tried something like this:

DELETE FROM wpbz_posts
WHERE `post_content` like '<h1>%</h1>';

but don't have a clue, how to work with text between tags, because every text line is different than others.

Any hints or help? Thanks!

LukStorms
  • 28,916
  • 5
  • 31
  • 45
Jaska Korhonen
  • 23
  • 2
  • 1
    If I read this correctly, you don't want to delete rows, you want to update columns. Look into the REPLACE function: https://www.w3schools.com/sql/func_sqlserver_replace.asp – Ben Jan 09 '19 at 06:32
  • Oh, missed the bit about needing to remove the text between them as well. What database are you using? – Ben Jan 09 '19 at 06:37
  • 1
    Please provide sampe input and your expected output. – Pham X. Bach Jan 09 '19 at 06:45
  • Yes exactly! Need to remove whole line

    sometext

    . I'm using MariaDB     – Jaska Korhonen Jan 09 '19 at 06:49
  • In MySql 8+ you can do it as described here: https://stackoverflow.com/a/49925597/2610061, MariaDB as of 10.0.5 offers something similar: https://mariadb.com/kb/en/library/regexp_replace/ – Carsten Massmann Jan 09 '19 at 06:59

1 Answers1

1

You could UPDATE with a REGEXP_REPLACE to strip the "h1" tags and what's in them

UPDATE wpbz_posts 
SET post_content = REGEXP_REPLACE(post_content,'<h1>.*?</h1>','') 
WHERE post_content LIKE '%</h1>%'

The ? in .*? makes it a lazy search, so it only matches till the first </h1> that follows the <h1> .

Test on db<>fiddle here

There's also an UPDATEXML function, but that only replaces 1 occurence of a tag.
And will raise an "Incorrect XML value" error if the string doesn't contain a valid XML. 

UPDATE wpbz_posts 
SET post_content = UpdateXML(post_content,'//h1[1]','') 
WHERE post_content LIKE '%</h1>%'
LukStorms
  • 28,916
  • 5
  • 31
  • 45