Fastest way to find, fetch and replace part of the string in database

Question

I have a set of records in database table and one of the columns has string value. Something like HeyHelloWorld1 or HeyGoodDayWorld2 or HeyHowdyWorld32. I need to change them all to GoodEveningWorld (and whatever comes at the end of the string). I'm trying to run script but it takes too long and I was wondering if anyone knows of a fastest way to implement this

The script:

$con=mysqli_connect("localhost","user","password","db");
// Check connection
if (mysqli_connect_errno())
 {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }
 $str_to_look_for = 'Hey';
 $new_str = 'GoodEvening';
 $result = mysqli_query($con,"SELECT * FROM table1 WHERE item LIKE '%$str%'");
 if(!$result) echo "No records found?";
  else
    {
  echo " Found ".mysqli_num_rows($result)." rows<br/>... Executing script...";
      while($row = mysqli_fetch_array($result))
         {
          $val = strstr($row['item'], '/World');
          $new_val = $new_str.$val;
          $id = $row['ID'];
          insert($new_val, $id, $con);
        }
        echo " DONE!";
    }


  function insert($x, $id, $con)
      {
    $result = mysqli_query($con,"UPDATE table1 SET item = '$x' WHERE ID = '$id'");
    if (!$result) echo "missed..<br/>";
      }

Andresch Serj · Accepted Answer · 2014-04-03 08:58:49Z

1

Check out the MySQL String functions and consider replacing it directly in the database.

UPDATE table1 
SET item = 
  CONCAT(
    'GoodEvening',
    SUBSTRING( item, INSTR( item, 'World') ) 
  )

This would work for anything followed by World, rather than just HelloWorld or HayWorld.

edited Apr 3, 2014 at 8:58

answered Apr 3, 2014 at 8:51

Andresch Serj

37.8k16 gold badges63 silver badges106 bronze badges

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

2 Comments

Dims Over a year ago

It worked as a charm! A second instead of two minutes!! Amazing! Thanks Andresch!

Andresch Serj Over a year ago

@user3065540 you're welcome. Consider accepting my answer so this question get's marked as answered thus doesn't get any more unneeded attention.

Collectives™ on Stack Overflow

Fastest way to find, fetch and replace part of the string in database

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