A friend asked me if it was possible to sort on just a portion of a text field, but not necessarily the first word. For example, lets say you have a full name field that is formatted  as ‘[FirstName] [LastName]‘. There is a simple, if not flawless, way to do this with T-SQL string functions. First, you need to generate a small list of names to work with. I will use a table variable to do this. It is easy to spin up and is disposed of as soon as the query is done running.

declare @MyNames table (FullName nvarchar(20));
insert @MyNames
values ('Betty Ferguson'),('Sam L Jackson'),('Horatio Alger');

Note that I used the much simpler SQL Server 2008 syntax to add the names. If you are using SQL Server 2005, you will need to add the names like this:

insert @MyNames
values ('Betty Ferguson');
insert @MyNames
values ('Sam L Jackson');
insert @MyNames
values ('Horatio Alger');

Since you can’t control people adding a middle initial, I will find the last name by counting the number of characters starting from the right before I find a space character. The best function to use for this is charindex.

select charindex(' ', FullName) as 'LastNameLetterCount' from @MyNames;

The problem is that the charindex function counts from the left. In order to count from the right, I need to flop the text over by using the reverse function. This function will take a string of characters such as ‘My dog spot’ and return it as ‘tops god yM’. I also subtract 1 so that I only return the length of the last name, not the last name and the space.

select charindex(' ', reverse(FullName))-1 as 'LastNameLetterCount' from @MyNames;

This returns the following values:

LastNameLetterCount
——————-
8
7
5

Now I have the length of our last name. With this value, I can use the ‘right’ function to isolate the last name value. This function will return the number of characters we indicate starting from the right. For instance right(‘My String’,3) will return ‘ing’.

select right(FullName, charindex(' ', reverse(FullName))-1) as 'LastName' from @MyNames;

This will return the values:

LastName
———————
Ferguson
Jackson
Alger

However, I want to order by this field, not display it. To do that, I move the string functions to the ‘order by’ clause and only display the original field.

select   FullName
from     @MyNames
order by right(FullName, charindex(' ', reverse(FullName))-1);

This will return the requested field in the desired ascending order:

FullName
——————–
Horatio Alger
Betty Ferguson
Sam L Jackson

Of course to sort in reverse order, I could add the ‘desc’ flag to the order by clause:

select   FullName
from     @MyNames
order by right(FullName, charindex(' ', reverse(FullName))-1) desc;

FullName
——————–
Sam L Jackson
Betty Ferguson
Horatio Alger

You may have noticed the holes in this approach, such as the field only containing a first name, a two part last name, etc. However, for some quick querying on a known data set, this technique may get you pretty far.