Group by, ignore common words

[MRKisThatKid]

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.

 

[r937]

ORDER BY
case when left(title,4) = 'The '
then substring(title from 5
for length(substring)-4)
when left(title,3) = 'An '
then substring(title from 4
for length(substring)-3)
when left(title,2) = 'A '
then substring(title from 3
for length(substring)-2)
else title
end

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[MRKisThatKid]

Awesome! Looks easy to impliment too. Thanks so much

 

[jayjfadd]

I couldn't get this to work with mySQL. Any tips?

 

[r937]

glad you asked, because i just spotted the typos

ORDER BY
case when left(title,4) = 'The '
then substring(title from 5
for length(title)-4)
when left(title,3) = 'An '
then substring(title from 4
for length(title)-3)
when left(title,2) = 'A '
then substring(title from 3
for length(title)-2)
else title
end

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dnoeth]

There's no LEFT in ANSI SQL

"left(title, 4)" -> "substring(title FROM 1 FOR 4)"

And there's no need for "for" in substring:
substring(title from 5)

;-)

Dieter

 

[r937]

thanks dieter

i guess the LEFT function just came out of left field, eh



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)