How to order by certain part of a string?

Question

How to order by certain part of a string, assuming i have column codes which has values of the following format: LLL_NNN, example dGd_542. How can i order by the second part of the string which is numerical?

Answer 1

You should be able to just extract substring from your code.

order by SUBSTRING(codes, 4) asc

Answer 2

use the SUBSTRING function

SELECT * FROM tablename
ORDER BY SUBSTRING(codes FROM 4)

On very large tables this can lead to performance issues.

Answer 3

(First the obligatory comment on database structure)

It appears the you have two meaningful values encoded into the single column codes. If at all possible you should refactor your database so those are separate columns. The sorting problem then goes away.

(Now the answer using the existing database structure)

You can use an expression and order by that expression:

 SELECT c1, c2, c3, SUBSTRING_INDEX(codes, '_', 2) as code_value
    FROM table ORDER BY code_value