-
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
-
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))
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|