MySQL string Dynamic Replace

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 == Gallery == Random Text with nothing (just remove) with just MySQL ?

Currently working with this update

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

but I can't find a way to detect the random text between those two text snippets.

Yes, but I`m looking for a mysql solution and since this only will be done once manually for all content in that table by me, performance won't be a issue. — CGeorges, Dec 23 '13 at 14:18
You want to remove all the text starting from == Gallery == to == Sources ==? — rullof, Dec 23 '13 at 14:32

score 1 · Answer 1 · edited May 23 '17 at 12:28

You can't there is no function in MySQL supporting UPDATE with regex(How to do a regular expression replace in MySQL?). You have to load the content to a string then performe a php regex replace to the string with php to remove the randome text the update 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']);
}

?>

Gordon Linoff · Answer 2 · 2013-12-23T15:03:53.650

You can manually construct the string, with something like:

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

This assumes that the delimiters each only appear once in the overall string.

As an update this would be:

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

score 0 · Answer 3 · answered Dec 23 '13 at 14:25

0

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

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

answered Dec 23 '13 at 14:25

underscore

6,495
6
39
78

Mihai · Answer 4 · 2013-12-23T15:05:55.637

0

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

SQL Fiddle

Assuming the number of == instances is equal on all rows.If you want just the random text:

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

SQL Fiddle

edited Dec 23 '13 at 15:05

answered Dec 23 '13 at 14:46

Mihai

26,325
7
66
81

MySQL string Dynamic Replace

4 Answers4

Linked