SQL sorting only after the third character

Question

I have a field in my table in Mysql that is string type, with the following data:

Coluna
01DV
03DV
04DV

If you enter the value 02CA the field lays like this

Coluna
01DV
02CA
03DV
04DV

I need him to stay that way:

Coluna
02CA
01DV
03DV
04DV

how do I do this in Mysql ?

Answer 1

If you want to sort by the letters of the example first, but keep the numerical order:

SELECT * FROM cadastro ORDER BY SUBSTRING(nome, 3), nome

Which pretty much equals

SELECT * FROM cadastro ORDER BY SUBSTRING(nome, 3), SUBSTRING(nome, 1, 2)

The syntax of substring is: substring( caractere inicial, quantidade ).

Answer 2

Thus:

SELECT coluna
FROM (SELECT coluna, substring(coluna, 3) coluna1 FROM tabela4) AS T 
ORDER BY T.coluna1, T.coluna

Example: Sqlfiddle

Answer 3

All I’ve done is make the substring combined with order by as in the example below.

SELECT * FROM cadastro WHERE 1 order by SUBSTRING(nome,5)

This way he orders after the 5th character.