How to remove ¶ (pilcrow) sign from database records

My aim is to remove ¶ (pilcrow) sign from the database records. There are 开发者_运维技巧thousands of records so I cannot do it manually. Is there any script available to remove ¶ (pilcrow) sign from MySQL database column?

UPDATE table1 SET myfield = REPLACE(myfield,'¶','') WHERE myfield LIKE '%¶%'

If you want to replace ¶ with an enter do:

UPDATE table1 SET myfield = REPLACE(myfield,'¶','\n') WHERE myfield LIKE '%¶%'
-- linefeed
or
UPDATE table1 SET myfield = REPLACE(myfield,'¶','\r\n') WHERE myfield LIKE '%¶%'
-- cariage return+linefeed.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Make sure the collation and charset of the connection and the column in question are the same:

DESCRIBE table1;
--  copy the column charset and collation
SET NAMES '<insert charset name>' COLLATE '<insert collation name>';

Now rerun the query.

See: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

Here's how I solved this (yes, major necro, but hey, I found this old thread, so someone else might also!):

I edited the column directly using phpMyAdmin, and discovered that the database was displaying a pilcrow (¶), but it was storing some other representation in the background. No pilcrow displayed when editing the value, only a carriage return. I selected everything after the character that immediately preceded the pilcrow (the pilcrow was the last character in the column), used that invisible string in my query, and it worked. The working query looked like this:

UPDATE myTable SET myCol=REPLACE(myCol,'
','') WHERE myKey>'myValue'

Note that the query was one line of code, not two. The invisible carriage return only makes it look like two lines.

I hope this helps someone! I investigated/tried lots of other suggestions, but none of them worked.