Sorting On Text Within A Field

I have a field in a table for name. In this table there are entries like:

John Smith
Mary Jones
Susan Johnson
Bill Armstrong

Naturally, when I do a query and ORDER BY name they come out as:

Bill Armstrong
John Smith
Mary Jones
Susan Johnson

It does its order by the first name, which is what you would expect, but, it is not what I want. Is there a way to get it to sort by last name even though last name is not a field?

It is not a database I have any control over with respect to changing fields , so I have to deal with the fields as they are.

You can order it by extracting last name as text after first space character. Look for CHARINDEX and SUBSTRING examples for extracting last name, you can then use the result in ORDER BY

Quote:

---

Originally Posted by skhanal

You can order it by extracting last name as text after first space character. Look for CHARINDEX and SUBSTRING examples for extracting last name, you can then use the result in ORDER BY

---

Thank you...after a little trial and error I got it. For those who are interested, here is what worked for me.

Code:

---

ORDER BY SUBSTRING(Name_Of_Field, _
CHARINDEX(' ',Name_Of_Field)+1, _
LEN(Name_Of_Field))

---