How to order by last name in the full name column?

Question

I have an SQL table with a FullName column that contains, for example, "John Smith".

How to sort data by name that appears in FullName column?

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

So the names are kept in order

• Name
• Middle names
• Surname

I am using Microsoft SQL Server 2005.

Answer 1

If 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

Answer 2

I would do something like:

SELECT FullName
FROM TABLE
ORDER BY REVERSE(SUBSTRING(REVERSE(FullName), 0, CHARINDEX(' ', REVERSE(FullName)))) 

Answer 3

, , , , , .

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

LastName Customer, , , . PERSISTED , , .

: :

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

, , , . , case . , .

Answer 4

, FullName, :

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)

:

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

(3 row(s) affected)

Firstname, MiddleName LastName. , , , FullName .

Answer 5

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))

:

fullname                   (No column name)
John Davis                  Davis
Harry Smith                 Smith
Allision Thomas Williams    Williams

Answer 6

:

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

Answer 7

, . , ", " -

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

, , , .

Answer 8

Query

SELECT stringrowname FROM tablename 
ORDER BY SUBSTRING_INDEX((stringrowname)," ",-1);

.