MySQL query to replace all occurences of a regex in a string

Question

I have a lot of bad data in a table and was hoping there's a query I can use to edit this data.

[{"type":"user","id":"584851"},"2":{"type":"user","id":"180269"},"3":{"type":"user","id":"140535"}]

and here's how I need it to look...

[{"type":"user","id":"584851"},{"type":"user","id":"180269"},{"type":"user","id":"140535"}]

So basically removing all occurrences of ' "2": ' (the number will differ)

Is there a simple query to do this or will I need to write something in php to do the job?

MySQL doesn't bundle a regex replace function. You'll have to use some user-edfined function for the same if you have privileges. github.com/hholzgra/mysql-udf-regexp — hjpotter92
– hjpotter92, Commented Sep 16, 2015 at 10:16

Ozzy · Accepted Answer · 2015-09-16 10:20:56Z

1

Unfortunately, MySQL does not have a REGEX_REPLACE function or equivalent that would let you run a simple update query. The next best thing I would suggest is to loop through every record in your table using your language of choice and applying the regex replace on it and saving. I have put an example in PHP below:

$db = new PDO(sprintf('mysql:host=%s;dbname=%s', $host, $dbname), $user, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = $db->query('select * from `table`');

foreach ($query->fetch(PDO::FETCH_OBJ) as $row) {

    $update = $db->prepare('update `table` set `data` = ? where `id` = ?');
    $update->execute([
        preg_replace('/"\d+":{/', '', $row->data),
        $row->id
    ]);
}

Assumptions have been made about your table structure but the important part of the code is the preg_replace parameter which will match what you need to remove.

answered Sep 16, 2015 at 10:20

Ozzy

10.7k28 gold badges99 silver badges140 bronze badges

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

2 Comments

Rick James Over a year ago

MariaDB does have such a function.

Ozzy Over a year ago

I answered with the assumption that he only has MySQL and does not want to, or cannot, mess around with the database server.

Collectives™ on Stack Overflow

MySQL query to replace all occurences of a regex in a string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related