Mysql sorting of alphanumeric values without considering alphabet

Question

In database, it store values are

is there a way to sort it correctly? like

I've never seen this. It is sorted correctly, by ascii code. Without knowing if it exists or not, it would be chaos to do this with a replace at runtime. You could have a second column for sorting purposes that will expand the numeric value. You'd run into problems, meaning time-consuming literal programming, due to things like 4 = 'four', but 40 = 'forty' (not fourzero). — ps2goat
– ps2goat, Commented Oct 3, 2013 at 4:03

Ja͢ck · Accepted Answer · 2013-10-03 04:01:11Z

2

Assuming there can be at most one letter before the digits start, you could use a condition like this in your sorting definition:

ORDER BY CAST(IF(col REGEXP '^[a-z]', SUBSTRING(col, 2), col) AS SIGNED)

Unfortunately, MySQL doesn't have a replace function that can handle regular expressions, otherwise that would have been very helpful at this point.

You may also want to consider storing the numeric value itself in a separate calculated field for more efficient sorting.

answered Oct 3, 2013 at 4:01

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1