Running a SQL query to replace certain characters in a table

We have a database that contains many characters and symbols we no longer want (or that we need to replace with something else). For example... We have some records in a database that contain the copyright symbol:

©

And we want to replace that with:

©

Our table is titled Product_Descriptions and the column we want to run this query on is ProductDescription.

I've already searched and I found this:

UPDATE <table>
SET name = REPLACE(LTRIM(RTRIM(name)), '©', '&copy;')

That should work right? But what do I enter to run this on my specific table/column? I know I have to replace the <table> from above with my own input... but can someone guide me in the right direction. I'm a noob when it's comes to SQL queries.

 

Go to your cpanel
and then to mysql admin

select the table

And select query part (not sure what they call it)

And type you query and click Go

Thanks

 

Sorry... I should have clarified... this on on MS SQL.

 

An update...

I found the following command to run against my database:

UPDATE Products_Descriptions
SET ProductDescription = REPLACE(LTRIM(RTRIM(ProductDescription)), '©', '&copy;')

However... when I try to run it it throws the following error:

Server: Msg 8116, Level 16, State 2, Line 1
Argument data type text is invalid for argument 1 of rtrim function.

Any ideas?

 

I tried:

UPDATE Products_Descriptions
SET ProductDescription = REPLACE(ProductDescription,'©','&copy;')

But got this error:

Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

 

I've done some more research and it appears since the database column is of type TEXT I can't run the replace function.

So... next best thing is to write a custom ASP Script. This isn't the right forum for this so I created a new thread here:

http://forums.digitalpoint.com/showthread.php?p=2098579

If anyone can help me I'd really appreciate it

 

I don't know ASP but in PHP it'd just be something like:

$replace = array(
'©'  =>  '©',
);

$conn = mssql_connect('SQLSERVER', 'user', 'password') or die('Could not connect');
mssql_select_db('databasename', $conn) or die('No such database');

$res = mssql_query('SELECT ProductID,ProductDescription FROM Product_Descriptions',$conn) or die ('Query failed');

while ( $row = mssql_fetch_assoc($res) ) {
    foreach ( $replace as $old => $new ) {
      if ( strpos($row['ProductDescription'],$old) ) {
        $newDesc = str_replace($old,$new,$row['ProductDescription']);
        mssql_query("UPDATE Product_Descriptions SET ProductDescription = '$newDesc' WHERE ProductID=".$row['ProductID'],$conn);
      }
    }
}

mssql_close($conn);

PHP:

If there's a lot of rows, you might want to add some limits and do X at a time.

 

I gave you the ASP/MS SQL solution in your other thread, try that.