SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Location
    California
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Removing 'The' or 'A' on alphabetization

    Just wondering what would be a good way to alphabetize a db table .. so that the list may look something like this:
    Code:
    Hello World
    The Grail
    Gwar
    The Lily
    Modern Sports
    A Tale of Two Cities
    Is there a way to make "the" or "a" invisible, or unimportant? (this is concerning book titles)

    Thanks,
    Mike

  2. #2
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's probably a better way of doing it but you could do something like:
    Code:
    SELECT id, title, REPLACE( REPLACE( title, 'A ', '' ) , 'The ', '' ) AS atitle
    FROM books
    ORDER BY atitle

  3. #3
    American't awestmoreland's Avatar
    Join Date
    Sep 2002
    Location
    Grand Rapids, MI
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Won't that replace all instances?

    i.e.

    "The Cat In The Hat" becomes "Cat In Hat"




    Andy
    From the English nation to a US location.

  4. #4
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, but as the search only needs to operate on the first letter of the book then it doesn't matter that more than one occurance might be replaced as long as the first one is. It still returns the full titles (with the A and The) just doesn't use them in the ORDER BY part.

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The exact same question was posted earlier this week. Here was the best solution:
    Code:
    SELECT title FROM myTable
    ORDER BY
    TRIM( LEADING 'The ' FROM 
    TRIM(LEADING 'A ' FROM title)
    )
    It was also suggested that if you are going to do that query a lot, it would be more efficient to create a field called something like title_for_sort, in which you store each title without any leading the's or a's, and then order by that field in your queries.

  6. #6
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that's better than using REPLACE.

  7. #7
    American't awestmoreland's Avatar
    Join Date
    Sep 2002
    Location
    Grand Rapids, MI
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by markl999
    It still returns the full titles (with the A and The) just doesn't use them in the ORDER BY part.
    Good point, well made
    From the English nation to a US location.

  8. #8
    SitePoint Enthusiast
    Join Date
    Dec 2005
    Location
    California
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Amazing, thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •