Group by, ignore common words

When using GROUP BY to sort the results of a select statement, is it
possible to have it ignore certain common words. i.e "The", "A".
For instance, say you were sorting a list of book titles; you would
want "The Promise of Happiness" to be near "Pride and Prejudice", not
"Theatre Talk: Voices of Irish Theatre Practitioners".
Thanks.A) GROUP BY is not used for sorting. Use ORDER BY for sorting. GROUP BY
can use other algorithms than sorting for what it does (grouping) so it
can't be trusted to always return a sorted order.
B) Not easily... Can you store 'The Promise of Happiness" as "Promise of
Happiness, The" ?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"never-aways" <nospam@.nospam.com> wrote in message
news:rbtr21l5lnfu33brj4it87rornnf9ubfcl@.
4ax.com...
> When using GROUP BY to sort the results of a select statement, is it
> possible to have it ignore certain common words. i.e "The", "A".
> For instance, say you were sorting a list of book titles; you would
> want "The Promise of Happiness" to be near "Pride and Prejudice", not
> "Theatre Talk: Voices of Irish Theatre Practitioners".
> Thanks.|||What about creating a user defined function that returns the "stripped"
value? You could order by on that.|||What about using this in the order by
SELECT
BookTitle
FROM
BookTitles
ORDER BY
REPLACE(SUBSTRING(BookTitle, 1, 4), 'The ', '') +
SUBSTRING(BookTitle, 5, LEN(BookTitle))
It should work, as long as there aren't any other characters except a space
after "the".
Simon Worth
"never-aways" <nospam@.nospam.com> wrote in message
news:rbtr21l5lnfu33brj4it87rornnf9ubfcl@.
4ax.com...
> When using GROUP BY to sort the results of a select statement, is it
> possible to have it ignore certain common words. i.e "The", "A".
> For instance, say you were sorting a list of book titles; you would
> want "The Promise of Happiness" to be near "Pride and Prejudice", not
> "Theatre Talk: Voices of Irish Theatre Practitioners".
> Thanks.