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
– CGeorges, Commented Dec 23, 2013 at 14:18
You want to remove all the text starting from == Gallery == to == Sources ==? — rullof
– rullof, Commented Dec 23, 2013 at 14:32

Community · Accepted Answer · 2017-05-23 12:28:41Z

1

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']);
}

?>

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Dec 23, 2013 at 15:20

rullof

7,5027 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

underscore · Accepted Answer · 2013-12-23 14:25:12Z

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, 2013 at 14:25

underscore

6,9277 gold badges44 silver badges82 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-12-23 15:03:53Z

0

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)
                         );

edited Dec 23, 2013 at 15:03

answered Dec 23, 2013 at 14:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Mihai · Accepted Answer · 2013-12-23 15:05:55Z

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, 2013 at 15:05

answered Dec 23, 2013 at 14:46

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

MySQL string Dynamic Replace

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related