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
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(*);
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.
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.