tags:

views:

86

answers:

7

I have a SQL Table with a column called FullName which contains, for example, "John Smith".

How can order the data by the last name that appears in the FullName column?

For a long name like "Laurence John Fishburne", I would like to order the data by the word "Fishburne".

Thus, names are stored in the order

  • First Name
  • Middle Names
  • Last Name

I am using Microsoft SQL Server 2005.

A: 

It really depends on how names are stored. Assuming "Last, First Middle" you could do something like

order by substring(0, charindex(',', FullName))

You may have to fiddle with it a little, but I believe that should work.

AllenG
and in addition, if LastName is the right most token in the string then use the reverse() function along with the solution above.
John Sansom
Please re-read the question. Also, your syntax for the substring function is invalid.
GateKiller
@GateKiller - my response was written prior to the re-clarification in the original question.
AllenG
+1  A: 
create table foo(fullname varchar(100))
go

insert into foo values ('Harry Smith');
insert into foo values ('John Davis');
insert into foo values ('Allision Thomas Williams');

SELECT fullname
     , REVERSE(left(REVERSE(fullname), charindex(' ',REVERSE(fullname))-1))
  FROM foo
ORDER BY REVERSE(left(REVERSE(fullname), charindex(' ',REVERSE(fullname))-1))

Output:

fullname                   (No column name)
John Davis                  Davis
Harry Smith                 Smith
Allision Thomas Williams    Williams
dcp
you don't need to use that many function calls (REVERSE+left+REVERSE+charindex+REVERSE) to determine the last string of the FullName. A single RIGHT+CHARINDEX+REVERSE should be all you need, see my answer.
KM
+2  A: 

I would do something like:

SELECT FullName
FROM TABLE
ORDER BY REVERSE(SUBSTRING(REVERSE(FullName), 0, CHARINDEX(' ', REVERSE(FullName)))) 
Recep
while not the most efficient/optimal solution this works, why the -1??
KM
A: 

When in doubt, do it yourself:

Select
*
From
Users
Order By
LTrim(Reverse(Left(Reverse(FullName), CharIndex(' ', Reverse(FullName))))) Asc,
FullName Asc -- Fall-over for when there isn't a last name
GateKiller
+1  A: 

try this, it uses the minimal number of functions to find the last space in the FullName string, which should help performance:

DECLARE @YourTable table (FullNamevarchar(30))

INSERT @YourTable VALUES ('Harry Smith');
INSERT @YourTable VALUES ('John Davis');
INSERT @YourTable VALUES ('Allision Thomas Williams');

SELECT
    FullName
        ,RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS SortBy
    FROM @YourTable
    ORDER BY RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)

OUTPUT:

FullName                       SortBy
------------------------------ ------------------------------
John Davis                     Davis
Harry Smith                    Smith
Allision Thomas Williams       Williams

(3 row(s) affected)

For the best performance and most accurate sort, you need to split out the name into Firstname, MiddleName, and LastName fields. Only the user entering the data can really understand what portion of FullName is the last name.

KM
This fails if the column only has a single name.
GateKiller
@GateKiller, use this, will work for a NULL name and a name with no spaces: `RIGHT(fullname, ISNULL(NULLIF ( CHARINDEX(' ', REVERSE(fullname)) - 1 ,-1),LEN(fullname)))`
KM
+1  A: 

This will do the trick:

create table #tmpTable
(
    ID int identity(1,1) primary key,
    FullName varchar(100) not null
);

insert into #tmpTable(FullName) values('Big John Sansom');
insert into #tmpTable(FullName) values('Mike Douglas Reid');
insert into #tmpTable(FullName) values('First Second Last');
insert into #tmpTable(FullName) values('JustOneTokenForName');

select 
    FullName,
    LastName = case 
        when CHARINDEX(FullName,' ') = 0 THEN FullName
        else RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)
    end
from #tmpTable
order by LastName

drop table #tmpTable
John Sansom
This fails if the column only has a single name.
GateKiller
@GateKiller: Perhaps it would helpful to all readers if you were to qualify requirements such as these in your question. Solution now updated to include this additional requirement.
John Sansom
+2  A: 

Instead of calculating what the last name is each time you want to run the query, you can have a computed column that persists the actual value into a column that can be used as you would any other column.

ALTER TABLE Customer
    ADD LastName AS 
        RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) PERSISTED

This Adds the column LastName to your Customer table, uses the function specified to calculate the value of the last name, and stores it onto the physical table. The keyword PERSISTED at the end is required for the value to be saved to the disk, else it will be computed each time a query is run.

Edit: To deal with values with no spaces:

ALTER TABLE Customer
    ADD LastName AS 
    case when CHARINDEX(' ', REVERSE(FullName)) > 0
    then RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) 
    else
    FullName
    end
    PERSISTED

Though you can fiddle with this function as you will to determine what happens in this case. My point is to show that case statements can be used. You may also want to cast all output paths to the same type to avoid type mismatches.

Josh Smeaton
This will only work in Sql Server 2005+
Josh Smeaton
This fails if the column only has a single name.
GateKiller
+1 for suggesting the computed column - all other solutions will not be able to use indexes. Although the best route would be to use this as an interim solution until the name can be separated into multiple columns and the front-end/reports/etc. changed to handle that.
Tom H.