How can I sort an SQL query but have certain UTF-8 characters be ordered as their normal equivalent? (e.g. É be regarded as E etc)

Question

I have a table of character names in a mySQL database.

I am trying to query the table and sort them alphabetically by name.

Some of the characters have names like "The Dagda" and the "The " needs to be ignored so I am attempting to use:

select character_id, name from characters where is_del=0 order by trim('The ' from name)

Which seems to work...

Some of the other characters have UTF-8 characters in their names such as "Ériu"

However now when my table is returned I get these "É" entries listed between "A" & "B".

I.E.:

Aengus Amergin Ériu Balor Banba etc.

Preservation of these UTF characters is crucially important on the front end.

Does anyone know a method where I could have these "É" characters and similar be represented as "E" for purposes of sorting, but will still render in the dataset as what they actually are?

I am thinking before asking this that this may not be possible but I am hoping someone here might have run into a similar problem before and might have a workaround.

Thanks in advance.

EDIT: changed UTF-16 to UTF-8 (my bad)

EDIT @Rick James :

I could not format this readably in a comment but the hex of the query is as follows:

name | hex(name)

Aengus Óg | 41656E67757320C383E2809C67
Amergin | 416D657267696E
Ériu | C383E280B0726975
Balor | 42616C6F72
Banba | 42616E6261

The 3rd item down is Ériu - I am not sure why they are rendering as above but this is what is being displayed through the phpmyadmin interface when I run the query select character_id, name, hex(name) from characters order by trim('The ' from name)

The first character's full name should be Aengus Óg (I am assuming this is again down to character set or collation but I am unsure so apologies for the ignorance on my part here)

Answer 1

"Double encoding" seems to be the problem. I discuss this somewhat in Trouble with UTF-8 characters; what I see is not what I stored

Should `

41 65 6E 67 75 73 20 C383 E2809C 67

Óg is hex C393 67 in UTF-8.

Latin1 hex C3 93 67 is Óg

Repeat to get C383 E2809C 67

CONVERT(BINARY(CONVERT('Aengus Óg' USING latin1))
               USING utf8mb4) --> 'Aengus Óg'

This seems to be "double encoding":

CONVERT(BINARY(CONVERT(CONVERT(UNHEX('C383E280B0726975') USING utf8mb4) USING latin1)) USING utf8mb4) --> 'Ériu'

With Ériu as an intermediate step. This explains why it sorted with the A's.

This is a common problem. It often goes unnoticed because browsers "fix" the mess.

Experiment with SELECTs against the table. If the first one works for you, then it is just Mojibake.

SELECT CONVERT(BINARY(CONVERT(my_column USING latin1))
               USING utf8mb4)
    FROM ... WHERE ...;

Read that other Q&A to see what steps went wrong to cause the problem. It likely involves storing UTF-8 characters in a column declared latin1.

ALTER TABLE ... CONVERT TO ... assumes that the data is correctly stored. But it wasn't. Now you have the CHARACTER SET correctly set on the columns, but the data in it has been Mojibaked. So, it needs something like

UPDATE tbl  SET
    col1 = CONVERT(BINARY(CONVERT(col1 USING latin1))
           USING utf8mb4),
    col2 = CONVERT(BINARY(CONVERT(col2 USING latin1))
           USING utf8mb4),
    ...
    ;

More on the fix: http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases

Rollback? If you are more comfortable rolling back to before the CONVERT TO, then ignore most of what I said before, then you need the 2-step ALTER after the rollback. (See that blog link.)