MySQL Dynamic Replace String

Question

I have a TEXT field that contains a lot of text, at some point I have == Gallery == Random text ==Source==.

I want to know if there is a way to replace with == Gallery == Random Textanything (just delete) only with MySQL?

Currently working with this update.

update `wiki_article_revisions` 
set `opcode` = replace(`opcode`, '== Gallery == ==Sources==', '');

but I cannot find a way to detect random text between these two pieces of text.

Answer 1

You do not have a function in MySQL that supports UPDATE with regex ( How do I replace a regex in MySQL? ). You need to load the contents into a string and then replace php regex with a php string to remove the randome text with the new content:

<?php

$con = mysqli_connect("localhost","username","password","db_name");
$result = mysqli_query($con,"SELECT * FROM wiki_article_revisions");

$regex = '/(\(== gallery ==\))([^\0]*)(\(== source ==\))/';

while($row = mysqli_fetch_array($result))
{
    preg_replace($regex, "$2", $row['opcode']);
    $mysqli_query("UPDATE wiki_article_revisions SET opcode=" . $row['opcode'] . "WHERE id=" . $row['id']);
}

?>

Answer 2

- :

select concat(left(val, locate('== Gallery == ', val) - 1),
              substring(val, locate('==Source==', val) + 10)
             )

, .

update :

update `wiki_article_revisions` 
    set `opcode` = concat(left(opcode, locate('== Gallery == ', opcode) - 1),
                          substring(opcode, locate('==Source==', opcode) + 10)
                         );

Answer 3

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace

update `wiki_article_revisions` 
set `opcode` = REPLACE(opcode, '== Gallery == ==Sources==', '');

Answer 4

UPDATE wiki_article_revisions
SET opcode = REPLACE(opcode,SUBSTRING_INDEX(opcode,'==',3),'')

SQL Fiddle

Assuming the number of instances ==is equal for all rows. If you need only random text:

UPDATE wiki_article_revisions
SET opcode = (SUBSTRING_INDEX(SUBSTRING_INDEX(opcode,'==',3),'==',-1))

SQL Fiddle