CODEWITHSUNDEEP
mysqlregex
CGeorges
CGeorges

Reputation: 488

MySQL string Dynamic Replace

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.

Upvotes: 1

Views: 1611

Answers (4)

rullof
rullof

Reputation: 7434

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

?>

Upvotes: 1

Mihai
Mihai

Reputation: 26804

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270733

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

Upvotes: 0

underscore
underscore

Reputation: 6887

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

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

Upvotes: 0

Related Questions