How to detect UTF-8 characters in a Latin1 encoded column – MySQL

by

Character encoding, like time zones, is a constant source of problems.

What you can do is look for any “high-ASCII” characters as these are either LATIN1 accented characters or symbols, or the first of a UTF-8 multi-byte character. Telling the difference isn’t going to be easy unless you cheat a bit.

To figure out what encoding is correct, you just SELECT two different versions and compare visually. Here’s an example:

SELECT CONVERT(CONVERT(name USING BINARY) USING latin1) AS latin1, 
       CONVERT(CONVERT(name USING BINARY) USING utf8) AS utf8 
FROM users 
WHERE CONVERT(name USING BINARY) RLIKE CONCAT('[', UNHEX('80'), '-', UNHEX('FF'), ']')

This is made unusually complicated because the MySQL regexp engine seems to ignore things like \x80 and makes it necessary to use the UNHEX() method instead.

This produces results like this:

latin1                utf8
----------------------------------------
Björn                Björn

More Related Contents:

  1. Trouble with UTF-8 characters; what I see is not what I stored
  2. What is the difference between utf8mb4 and utf8 charsets in MySQL?
  3. Differences between utf8 and latin1
  4. Fetching UTF-8 text from MySQL in R returns “????”
  5. How to fix double-encoded UTF8 characters (in an utf-8 table)
  6. How to convert an entire MySQL database characterset and collation to UTF-8?
  7. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  8. Change MySQL default character set to UTF-8 in my.cnf?
  9. How to make MySQL handle UTF-8 properly
  10. “Incorrect string value” when trying to insert UTF-8 into MySQL via JDBC?
  11. How to conduct an Accent Sensitive search in MySql
  12. How to display UTF-8 characters in phpMyAdmin?
  13. How can I find non-ASCII characters in MySQL?
  14. UTF-8: General? Bin? Unicode?
  15. Save Data in Arabic in MySQL database
  16. How to remove accents in MySQL?
  17. Convert latin1 characters on a UTF8 table into UTF8
  18. What are the differences between utf8_general_ci and utf8_unicode_ci? [duplicate]
  19. Incorrect string value: ‘\xF0\x9F\x8E\xB6\xF0\x9F…’ MySQL
  20. MySQL VARCHAR Lengths and UTF-8
  21. MySQL command line formatting with UTF8
  22. How to change the default charset of a MySQL table?
  23. MySQL distinction between e and é (e acute) – UNIQUE index
  24. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci
  25. Detecting utf8 broken characters in MySQL
  26. MySQL throws Incorrect string value error
  27. UTF-8 not working in HTML forms
  28. Mysql server does not support 4-byte encoded utf8 characters
  29. UTF8 problem with MySQL 5
  30. ERROR 1115 (42000): Unknown character set: ‘utf8mb4’

Leave a Comment