Results 1 to 3 of 3

Thread: Sorting On Text Within A Field

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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

  3. #3
    Join Date
    Dec 2009
    Posts
    79
    Quote Originally Posted by skhanal View Post
    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
  •