Posted by Peter Albertsson on 06/28/05 01:07

Hi,

I've a MySQL (MySQL 5.0.7-beta) database with movie information. One table
contains the movie titles. I want to select the distinct first alpha numeric
characters from all the movie titles. The problem is that some movie titles
look like this: '...And Justice for All', that is, the first letter is not
an alpha numeric letter.

My query now looks like this:

SELECT DISTINCT SUBSTRING(title, 1, 1) AS letter FROM movie_titles ORDER BY
letter

That means that '...And Justice for All' will be sorted under '.', but I
want that movie title to be sorted under 'A' in my application.

So I thought maybe I could use reg exps, but MySQL reg exp function only
returns 0 or 1, never the match.

Does anyone have any idea how I could query for the first alpha numeric
(0-9a-zA-z) character in a string?

Thanks in advance,

Peter Albertsson

• Next in forum: Re: Grouping system
• Prev in forum: Re: Connection Problem, Driving me insane
• Thread view: Selecting the distinct first alpha numeric characters from a column

[Reply to this message]