How to remove text between [mytag] some text [/mytag]tags from mysql db records?

I have a table called

stories

and there is a column

body

Each body contains from previous CMS a php/html code encapsulated between tags [soltag] [/soltag]

so e.g. one record look like this:

Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting
[soltag]ph开发者_运维百科p, $end = ""; if (isset($_GET["vote"])) { $end .= "?vote=" . $_GET["vote"]; if (isset($_GET["vid"])) { $end .= "&vid=" . $_GET["vid"]; }; }; $output = file_get_contents("http://example.com/something.html" . $end);[/soltag] And the body text continues here.

And I would like to remove everything between these tags including these tags from every record in database.

so, after this it should look like:

Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting  And the body text continues here.

I have like 5000 records so manually it will consume at least 2-3 days robot-like work.

Any thoughts how could I remove that with mysql commands?

Thanks in advance.

I was searching something similar for a similar purpose and that was really useful.

Just one thing... for the next that searches on the same issue.

If you want as well the closing tag to be removed you should sum the string length of the closing tag in the third argument of SUBSTRING :

UPDATE stories SET [body] = REPLACE([body],

                 SUBSTRING([body], 
                           INSTR([body],'[soltag]'), 
                           INSTR([body],'[/soltag]') +CHAR_LENGTH('[/soltag]') -INSTR([body],'[soltag]')
                          )
                 ,'');

Hope that helps someone! :)

Use SUBSTRING() and INSTR() to help in this task.

Before updating the column, ensure you have the right data.

SELECT REPLACE([body],
                     SUBSTRING([body], 
                               INSTR([body],'[soltag]'), 
                               INSTR([body],'[/soltag]') -INSTR([body],'[soltag]')
                              )
                     ,'') AS NewBody
From [stories]

Adjust above as needed if the INSTR() are off by one.

Then you can update all columns in this table.

UPDATE stories
SET [body] = REPLACE([body],
                     SUBSTRING([body], 
                               INSTR([body],'[soltag]'), 
                               INSTR([body],'[/soltag]') -INSTR([body],'[soltag]')
                              )
                     ,'')