FrustratedWithFormsDesigner FrustratedWithFormsDesigner - 6 months ago 14
MySQL Question

MySQL - find and fix incorrect characters

I have a table in MySQL that contains people's names and now some people are putting in characters with accents. Whatever original character set/collation this database used, it could not handle characters with accents and characters. Characters such as 'é' turned into 'é', characters such as 'ü' turned into 'ü' in the front-end applications that connect to this database. The database has since been modified (as well as most front-end applications that needed it) so that these fields use a UTF8 encoding and collation. Most names with accents now render correctly.

The problem is, there are some fields that now have a literal 'é' (some weird double-encoding caused by manually copying and pasting stuff? I don't know, I wasn't there!). I now need to find all of these and modify these fields to use the correct accented characters. To find them, I wrote a query (based on the table I found here):

select count(*), bad_char
from some_table
inner join (
select '€' as bad_char union
select '‚' as bad_char union
select 'Æ’' as bad_char union
select '„' as bad_char union
select '…' as bad_char union
...
-- snip a whole bunch
...
select 'þ' as bad_char union
select 'ÿ' as bad_char ) bad_chars
where some_table.some_text_field like CONCAT('%',bad_chars.bad_char,'%')
group by bad_char
order by count(*);


And I get results like this:


count(*), bad_char
------------------
'1', '¯'
'1', 'Ñ'
'1', 'Ö'
'1', 'Ž'
'1', 'Ç'
'1', '¬'
...snip...
'1797', 'ß'
'4450', 'Ê'


But I have a feeling this doesn't quite work the way I think it does. One of the "bad_char" combinations I'm searching for is 'Ê' but when I run a query like this:

select some_text_field from some_table where some_text_field like '%Ê%';


I get many results that are 'as' which are the same letters but without the accents. But for other cases such as '¯' the query seems to work fine.

How can I get this query to not treat 'as' the same as 'Ê'?

Answer

I get many results that are 'as' which are the same letters but without the accents.

That would be an issue of the collation used - those are rule sets for character comparison, and they define which characters are to be treated as equal in different languages.

But you can use the BINARYoperator to change that directly within the query.

Comments