Order by last 3 chars

Question

I have a table like:

id  name
--------
1   clark_009
2   clark_012
3   johny_002
4   johny_010

I need to get results in this order:

johny_002
clark_009
johny_010
clark_012

Do not ask me what I already tried, I have no idea how to do this.

Answer 1

you should try this.

SELECT * FROM Table order by SUBSTRING(name, -3);

good luck!

Answer 2

You may apply substring_index function to parse these values -

select * from table order by substring_index(name, '_', -1)

Answer 3

You can use MySQL SUBSTRING() function to sort by substring

Syntax : SUBSTRING(string,position,length)

Example : Sort by last 3 characters of a String

SELECT * FROM TableName ORDER BY SUBSTRING(FieldName, -3);
#OR
SELECT * FROM TableName ORDER BY SUBSTRING(FieldName, -3,3);

Example : Sort by first 3 characters of a String

SELECT * FROM TableName ORDER BY SUBSTRING(FieldName, 1,3);

Note : Positive Position/Index start from Left to Right and Negative Position/Index start from Right to Left of the String.

Here is the details about SUBSTRING() function.

Answer 4

If you want to order by the last three characters (from left to right) with variable name lengths, I propose this:

SELECT *
FROM TABLE
ORDER BY SUBSTRING (name, LEN(name)-2, 3)

The index starts at lenght of name -2 which is the third last character.

I'm a little late but just encountered the same problem and this helped me.