I am working with phpMyAdmin and I have Arabic character values in my name column like کلب الرقيÙ'Ø© علی العزیزبوری , I want to convert them into the correct form.
I tried the following query and it converted most of the records in the table into the correct form.
ALTER DATABASE alfeker_book CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE guestbook CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; UPDATE guestbook SET name = CONVERT(BINARY CONVERT(name USING latin1) USING utf8mb4);
I also tried other methods.
UPDATE guestbook SET name = CONVERT(BINARY CONVERT(name USING binary) USING utf8mb4);
But several of them remain the same and show the following error.
#1977 - Cannot convert 'utf8' character 0xD8AD to 'latin1'
I also tried this query. :
SELECT id,name, CONVERT(BINARY CONVERT(name USING latin1) USING utf8mb4) AS converted_name FROM guestbook;
This query runs fine and displays the unconverted characters perfectly. But when I use update query it doesn't work.
This is garbled or "double-encoded" Arabic text.
There are several ways to repair data. There is only one way to improve it, other ways will make it worse.
Have a look at this UTF-8 character issue; what I see is not what I'm storing and follow the suggestions therein about getting the hex value of what's in the database , in addition to providing SHOW CREATE TABLE defined by the current schema. Please also provide SHOW VARIABLES LIKE 'char%' and connection parameters.
Before backing up the various ALTERs and CONVERTs, please provide the hex values and I might choose the correct conversion method.