MySQL Fixing Strange Invisible UTF-8 Characters in SECONDS!

Amazon If you work with UTF-8 characters with MySQL your table may be filled with strange, invisible characters that you have no idea how to get rid of. Read more to find out how to fix weird, invisible characters in MySQL in mere SECONDS!

In my case I realized that a space in a column is not really a space; it's some UTF-8 invisible character. One problem this causes is processing on the data in that column with PHP or any programming language may fail. For example I am trying to replace a space with some HTML only to find that the replacement fails.

Make sure you have this problem

Let's connect to our MySQL database and run the following commands. Let's assume english_translation is the column in question and entry is the database table.

mysql> select english_translation from entry where entry_id=1;
+------------------------------+
| english_translation          |
+------------------------------+
| hunch; slump   slouched over |
+------------------------------+
1 row in set (0.01 sec)

Are the three spaces between slump and slouched really spaces? Let's find out.

mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select english_translation from entry where entry_id=1;
+-------------------------------+
| english_translation           |
+-------------------------------+
| hunch; slump ┬á slouched over |
+-------------------------------+
1 row in set (0.00 sec)

Oh no, after we set MySQL encoding to UTF-8 it turns out there's a strange character between slump and slouched.

Let's solve the problem

Let's replace the strange invisible character with a space with a MySQL command.

mysql> update entry set english_translation = replace(english_translation,'┬á',
' ') where entry_id=1 limit 1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Does it work? Let's find out.

mysql> select english_translation from entry where english_translation like '%   %' and entry_id=1;
+------------------------------+
| english_translation          |
+------------------------------+
| hunch; slump   slouched over |
+------------------------------+
1 row in set (0.00 sec)

Yes our problem is fixed! Hope my instructions will fix your problem too. If not let me know!

MySQL: How Do I Replace Non-ASCII Characters with ASCII Characters?

If you have any questions let me know and I will do my best to help you!