Sort fullname by lastname 2

[cleanair4me]

I have a field called fullname that outputs records with fullname of people.
Here is what I have when I do a simple sql:
Select fullname from tableOne

John Jones
Bill Aronsen
Sam Baker
George Williams
Dave Smith

I would like to sort in order of last name but cant figure out how to do the sql. Please advise.

 

[sem]

...order by substr(fullname, instr(fullname, ' ')+1)

Regards, Dima

http://seminihin.narod.ru

 

[dstxaix]

Don't people have middle initials like

John M. Doe

 

[SantaMufasa]

Don't people have middle initials like

John M. Doe

Yes, they have, dstxaix...Good catch.


The best way to programmatically deal with that issue is to modify slightly Sem's fine code suggestion:

...order by substr(fullname, instr(fullname, ' '[B][I],-1[/I][/B])+1)...

The "-1" addition to Sem's code searches for the first blank space from the reverse (right-side) end of the string.

Let us know, CleanAir, if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cleanair4me]

That is great info. Thanks!