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.
Click to continue »