Sometimes you need to do ordering by in a full name column. If you want to order by first name, that’s cool, but when you need to order by last name, you need to find the surname for each field.
Let’s take a look at this data:
|Leonardo da Vinci|
These values are ordered by first name so there’s no problem with that. But if we wanted to order by last name(which is not a particular column in this table), we had to find a way like splitting these fields or something else.
SELECT [Full Name], REVERSE(SUBSTRING(REVERSE([Full Name]), 0, CHARINDEX(' ', REVERSE([Full Name])))) AS SurnameAfterReversing FROM GENIUS_TABLE ORDER BY REVERSE(SUBSTRING(REVERSE([Full Name]), 0, CHARINDEX(' ', REVERSE([Full Name])))) --We need to select our ORDER BY expression as a new column otherwise it will warn that it can't order by a column which is not exist in query result.
It reverses the whole name here, then gets text until the first space (‘ ‘) character, then reverses the new text back. That way, we actually get the last name so you can do anything you want with this column.
And here is the data as a result of this query:
|Leonardo da Vinci||Vinci|