T-SQL: How to Order by Surname in Full Name Column?

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:

[Full Name]
—————————-
Albert Einstein
Leonardo da Vinci
Nicolas Tesla

 
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.

Anyway, there was a good line of code in StackOverflow (see question&answer here) written by user Recep.

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:

[Full Name] SurnameAfterReversing
—————————- —————————-
Albert Einstein Einstein
Nicolas Tesla Tesla
Leonardo da Vinci Vinci
1 Comment