how to remove a "pilcrow" character from a field

[solarisuser]

Hello All,

I have a field that has a few thousand lines of data with a ¶ after the data (like "John¶").  It happened after I imported a lot of data. 

I tried using "select rtrim(field) from table" but it doesn't seem to remove it.

Does anyone know what MySQL statement will remove it?

Thanks!!

[artacus]

I'm not sure but it sounds like you've got the wrong character set.

Try SELECT CONVERT(field USING latin1) FROM table and see what that does.

[solarisuser]

[quote author=artacus link=topic=117678.msg480295#msg480295 date=1165466989]
I'm not sure but it sounds like you've got the wrong character set.

Try SELECT CONVERT(field USING latin1) FROM table and see what that does.
[/quote]

It doesn't work -- it might be because I'm using an old version of mysql.. 4.0.X....

Any other suggestions? =(

[solarisuser]

bump because i edited initial question quite a bit

[artacus]

A pilcrow huh? I learned something new today.

See if this works:
UPDATE mytable SET myfield = REPLACE(myfield,'¶','')

If it doesn't you'll have to do it in PHP using
str_replace(chr(182),'',$row['myfield']);

you can use ord() and chr() to get to wierd non-printable characters.

[solarisuser]

thanks for the suggestion, but it didn't seem to work.

If I use the MySQL Query Browser, and edit the field, it actually shows the "pilcrow" expressed as a paragraph and if I just backspace to get rid of the newline it makes, and save the field, the pilcrow go away.

I can't use any PHP magic because I need to stop duplicate entries from going to PHP. 

Here's the issue:

One field has "Data", and another field has "Data¶".
When I do a "SELECT DISTINCT * from data", it shows both of those in a <select> dropdown, effectly showing two of the same names. (The select dropdown seems to ignore the pilcrow).

Any help is very much appriciated as I can't find any info on google about it!

Thanks

[quote author=artacus link=topic=117678.msg481212#msg481212 date=1165597972]
A pilcrow huh? I learned something new today.

See if this works:
UPDATE mytable SET myfield = REPLACE(myfield,'¶','')

If it doesn't you'll have to do it in PHP using
str_replace(chr(182),'',$row['myfield']);

you can use ord() and chr() to get to wierd non-printable characters.
[/quote]

[fenway]

You need to determine how MySQL has stored this non-ascii character first, before you can replace it.