0

I have a large mysql database with more than 8.000.000 products

At this moment The column 'productname' can contain values for example 'Tag_Heuer Watch "pilot" (red)'

I have millions of rows with non A-Z and 0-9 characters in the productname field

I want to remove all the strange characters in this field and just leave a-z and 0-9 and spaeces. So in this example i want this field to be 'Tag_Heuer Watch pilot red'.

Offcourse i can loop through all rows en str_replace the fields with the following function:

<?php
function stripJunk($string){
$string = preg_replace("/[^a-zA-Z0-9]/", " ", $string);
$string = preg_replace("/\s{2,}/"," ",$string);
$string = trim($string);
return $string;
}
?>

But this take hours :-(

What is the best fastest way to do this in probably 1 query?

Marcel Vermeer
  • 1
  • 2
  • possible duplicate of [How to do a regular expression replace in MySQL?](http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql) – Denis Oct 05 '13 at 07:21
  • 1
    It's better to act on your database data imediatly instead of retrieving the data via PHP and then send them back again. You spend a lot of resources. Try to update your database with a single UPDATE statement, and not all the records with one query. Limit the rows you want to update, because 8M records are too much !!! – KodeFor.Me Oct 05 '13 at 07:24

1 Answers1

0
  1. Select rows from sql using LIMIT. Try Your script to remove special characters and use update query to update rows.
  2. To do this add new column in mysql table row_updated int default 0; Once you update section of rows also update the row_updated to 1.
  3. Then you easily update records in group. My guess this is the best way to do this.
KodeFor.Me
  • 13,069
  • 27
  • 98
  • 166
Nes
  • 304
  • 1
  • 10