Posted by ZeldorBlat on 06/15/06 02:17

eklund@gmail.com wrote:
> ZeldorBlat wrote:
> > eklund@gmail.com wrote:
> > > Hello, I hope I can get some help on this. I really really really need
> > > to get this working!
> > >
> > > Here is an overview of what I am trying to do. I have a database of
> > > movie reviews, and I am trying to sort them alphabetically. (So I can
> > > select all reviews starting with $someletter) The catch is I don't want
> > > titles starting with The to be included with T. For example. "The
> > > Exorcism of Emily Rose" will be under E, not T. Preferably ignoring "A"
> > > as well, as in "A Nightmare on Elm Street" but I don't want to get
> > > greedy. :-)
> > >
> > > Here is what I got so far.
> > > $sreview = mysql_query ("SELECT * FROM reviews ORDER BY REPLACE(Title,
> > > 'The ', '') ASC");
> > >
> > > That will arrange it alphabetically, and it ignores "The" when sorting,
> > > but outputs it later on when I ask for the title. So far, so good.
> > >
> > >
> > > Next, I want to sort them for every review starting with $id (A letter,
> > > number, or group of letters, or a group of numbers.
> > >
> > > I figured out this:
> > > WHERE Title LIKE '$id%' //$id being a letter of the alphabet, or a
> > > number
> > >
> > > But I can't get that to work in my script. If I put it in front of the
> > > ORDER BY REPLACE it does not include the entries that have "The" in
> > > front of them. If I put it after the ORDER BY REPLACE it does not work
> > > at all.
> > >
> > >
> > >
> > > What I would like to have is a script that I can sort by a single
> > > letter/number, or a group of them. (0-9, A-I, etc.) And ignoring The,
> > > and hopefully A.
> > >
> > > Can someone help me out, or at least point me in the right direction.
> > >
> > > Thanks a lot!
> > > ~Rich
> >
> > Why not use the same technique in the where clause as you did in the
> > order by?
> >
> > SELECT *
> > FROM reviews
> > WHERE REPLACE(Title, 'The ', '') like 'F%'
> > ORDER BY REPLACE(Title, 'The ', '') ASC
>
> I did not know I could do that. (Newbie here!)

Welcome. Functions are expressions, and, in general, can be used just
like constants and column names in the select list, where clause, group
by, and order by. Keep in mind that functions can also be nested (i.e.
the return value of some function can be used as a parameter to
another).

> Is there a way to do the same for Titles starting in A?

C'mon...even a newbie can figure that out with a little bit of thinking
:) Change the 'F%' to 'A%'. The % is a wildcard that matches 0 or
more characters.

> Also, selecting a group of characters. Such as starting in A-I?

Instead of using the pattern 'F%' (an 'F' followed by zero or more
characters) use '[A-I]%' (a single character in the range A-I followed
by zero or more characters).

[Back to original message]