How to sort alphanumeric values in mysql?

Hi,

I have a table with a varchar field, in which there is alphanumeric data like following:

EV-489RY3DA
AB-34Y67846
E5-RMT893Y9

This col needs sorting but the E5 comes before EV, how to sort it so that EV comes before E5 ? AB is obviously coming on top.

Thanks.

1st question: why?

2nd question: how much pain are you willing to endure to achieve this?

1 Like

I don’t think it’s easy without some nasty workarounds. For example, replace all numbers with characters which are higher up in sorting order than letters in the Unicode Table:

SELECT * FROM table ORDER BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col, '0', 'Ā'), '1', 'ā'), '2', 'Ă'), '3', 'ă'), '4', 'Ą'), '5', 'ą'), '6', 'Ć'), '7', 'ć'), '8', 'Ĉ'), '9', 'ĉ') COLLATE utf8_bin;

This will work if col is in utf8 character set. If you want case insensitive sorting then use LOWER(col). I also make the assumption that you are only using basic letters and numbers like in your example.

For this I’ve chosen replacement characters in the range starting at 0100 hex. But you could do it with any other characters high enough, and you could do it also using a single byte collation like ascii and use characters below 0100 hex. Then for case insensitive sorting you could use something like COLLATE ascii_general_ci instead of LOWER.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.