Removing 'The' or 'A' on alphabetization

[ambig]

Just wondering what would be a good way to alphabetize a db table .. so that the list may look something like this:

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

[markl999]

There's probably a better way of doing it but you could do something like:

SELECT id, title, REPLACE( REPLACE( title, 'A ', '' ) , 'The ', '' ) AS atitle
FROM books
ORDER BY atitle

[awestmoreland]

Won't that replace all instances?

i.e.

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




Andy

[markl999]

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.

[7stud]

The exact same question was posted earlier this week. Here was the best solution:

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.

[markl999]

Yeah, that's better than using REPLACE.

[awestmoreland]

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

[ambig]

Amazing, thanks.