Find last alphanumeric value in mysql

Question

I have a field that contains usernames in a mysql db eg:

johndoe2
johndoe10
johndoe3

If i order by that field DESC I get:

johndoe3
johndoe2
johndoe10

I need to get:

johndoe10
johndoe3
johndoe2 

Any help appreciated

Answer 1

If performance isn't a concern, consider writing a function like this one to remove non-numeric characters, and then simply including the call in your ORDER BY.

 SELECT * 
 FROM MyTable
 ORDER BY 
      CAST(strip_alpha(UserName) as int) DESC

For a more performant + hassle-free solution, consider extracting the number from the username into a new column. Then you could easily ORDER BY:

 SELECT *
 FROM MyTable
 WHERE  UserName LIKE 'johndoe%'
 ORDER BY TrailingDigit DESC

Answer 2

This is a giant hack, but I think will work. If your column name is s:

order by 
  (replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(s, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''),
  length(s) desc,
  s desc

First sort by the alphabetic part of the text, to get all the same names together, then by length to get them sorted roughly numerically, then by s. The last order by s works properly now because it's only used to disambiguate between same names with the same number of digits.