Why does one query find instances when the other doesn't?

[Paul Schmehl]

We recently had a problem in our UBB forum, after an upgrade from mysql 5.7 to mysql 8.0, with punctuation marks and emojis being rendered as Windows-1252 characters instead of utf8. e.g “ = left quote = “

I finally got the issue corrected by adding these two lines to my.cnf's mysqld section: character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

(The db was already set to utf8mb4).

That left me with a cleanup job. (about 8.000 instances). So, I wrote a query to clean them up. However, I needed to exclude two forums where we were discussing the issues (because I didn't want to clean those up.)

So, I wrote this query, which did a great job of cleaning up the mess. (This is one query. I had to write several more to clean up post subjects, member signatures, and topic subjects as well.

This is the cleanup query:

Replace all Windows-1252 characters in post bodies with utf8 characters
UPDATE sbf_POSTS a
JOIN sbf_TOPICS t ON a.TOPIC_ID = t.TOPIC_ID
JOIN sbf_FORUMS f ON t.FORUM_ID = f.FORUM_ID
SET a.POST_BODY = REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(REPLACE
(a.POST_BODY, '“' , '“'),
'”','”'),
'‘','‘'),
'’', '’'),
'—', '–'),
'–', '—'),
'…', '᠁')
WHERE (
a.POST_BODY LIKE '%“%' OR
a.POST_BODY LIKE '%”%' OR
a.POST_BODY LIKE '%‘%' OR
a.POST_BODY LIKE '%’%' OR
a.POST_BODY LIKE '%—%' OR
a.POST_BODY LIKE '%–%' OR
a.POST_BODY LIKE '%…%'
)
AND f.FORUM_ID != '24'
AND f.FORUM_ID != '57'

Unfortunately, that didn't clean up ALL of them. I found 181 instances that were missed using this simple query.
select POST_BODY from sbf_POSTS where POST_BODY LIKE '%â€%'

My question is, why does the simple query find instances that the complex query does not? I'm assuming it's because of the joins, but I don't understand why those would create this problem.

Since I can't get the forum ids from the POSTS table, I can't use JOINS to do the remaining cleanup, unless I want to wipe out all the posts where we discussed these issues as well.