Posted by Peter Albertsson on 06/30/05 02:18

"Steve" <googlespam@nastysoft.com> wrote in message
news:1120041468.184938.89280@g44g2000cwa.googlegroups.com...
>
>> I want to select the distinct first alpha numeric
>> characters from all the movie titles.
>
> Rather than try to do this on-the-fly at SELECT time, you could create
> a new column and pre-populate it with a sortable version of the title.
>
> That still might not be trivial to do automatically because there is an
> element of opinion in where a title ought to be sorted (for instance,
> IMDB sorts "...And Justice For All" under "Others" not "A".) However,
> you could just strip out all punctuation other than spaces and then
> make the text all upper-case. In the context of MySQL + PHP it's
> probably less cumbersome to do that in your PHP code.
>
> Then for your specific query use...
>
> SELECT DISTINCT SUBSTRING( sortable_title, 1, 1) AS letter
> FROM movie_titles
> ORDER BY letter
>
> and for listing movies by title use...
>
> SELECT `title`, `director`, `year`, ...
> FROM movie_titles
> ORDER BY sortable_title
>
> ---
> Steve
>

Thanks.

I think I might go for a "Others" category...

Maybe a request to MySQL for better reg exp support would be in place. ;)

// Peter

[Back to original message]