[SOLVED] how to remove unwanted words/characters from database item

[pixeltrace]

hi,

 

i need help. currently i dump some data into our database from another database.

now, i found some wierd characters

 

at the end of each of those items.

and there are like 500 items in my database table which has those.

 

is there a way that i can remove it using mysql query to be run in mysql or phpmyadmin

something like

update * from table where field name item has  and replace it with just blank?

 

hope you could help me with this

 

thanks!

[Wes1890]

I came up with this script.. because Im sure you don't want to hand pick through all the DB rows yourself... I've had the same prob before... I haven't tested this though.. but I believe it would work.

Be sure to replace the table/column names with your own

<?php
// Get all rows from your database that contain either à or Â
$sql = "SELECT * FROM your_table WHERE string_column REGEXP '^[.][ÃÂ][.]$'";
$result = mysql_query($sql) or die("1. ".mysql_error());

// Put them into an array
$row = mysql_fetch_array($result);

// Count how many rows there are
$total_rows = count($row);

// If there are more than 0 rows, then replace each instance of à or  with nothing.
if ($total_rows > 1)
{
   for ($i=0; $i<$total_rows; $i++)
   {
      $this_id = $row[$i]['id_column'];

      $newstring = str_replace("Ã","",$row[$i]['string_column']);
      $newstring = str_replace("Â","",$row[$i]['string_column']);

      mysql_query("UPDATE your_table SET string_column='".$newstring."' WHERE id_column=".$this_is."") or die("2. ".mysql_error());
   }

// Done..
?>

[darkfreaks]

you can manually go into the database and delete them but

 

use

str_replace('Ã','');

 

to prevent it from entering in the first place.

[pixeltrace]

hi.

 

thanks for all the help, really appreciate it.

i was able to come out with a solution and it worked.

UPDATE my_table SET `my_field` = replace(`my_field`, "old_text", "new_text") 

 

i runned this in mysql from the server.

 

thanks!

[Wes1890]

^ damn! it was that easy! after all that typing..

 

lol.. well atleast i had something to do while waiting on the tv to come back on (power outage... gay)