Posted by eklund on 10/14/96 11:50

strawberry wrote:
> eklund wrote:
>
> Is there a way to have both The, and A in a replace statement?
> Everything I've read indicated that it's only for one string of
> characters, so I've tried using 2 replaces in a row, but that didn't
> work either.
>
> Re-read the answer above
>
>
> eklund@gmail.com wrote:
> > ZeldorBlat wrote:
> > > eklund@gmail.com wrote:
> > > > ZeldorBlat wrote:
> > > > > 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).
> > > >
> > > > Thanks. I did know that I could change 'F%' to 'A%', (I was actually
> > > > using a $id so I can change it through my reviewsort.php?id=D I ment
> > > > movies starting with A, such as "A Nightmare on Elm Street" Sorry for
> > > > the misunderstanding. I don't think I could have found a way onto the
> > > > internet if I couldn't figure that out! lol (Ignoring 'The ', and 'A '
> > > > - just like in your example, which I still can't figure out whats wrong
> > > > with it. Looks nice though. :-) )
> > >
> > > The query should work in both cases. Consider the following:
> > >
> > > REPLACE('The ', '', 'A Nightmare on Elm Street') gives you 'A Nightmare
> > > on Elm Street'
> > > REPLACE('The ', '', 'The Nightmare on Elm Street') gives you 'Nightmare
> > > on Elm Street'
> > >
> > > In other words, the value of REPLACE(...) for something that doesn't
> > > begin with 'The ' will just be the same string (except in the case
> > > where 'The ' appears somewhere else in the string). But you get my
> > > point...
> >
> > Is there a way to have both The, and A in a replace statement?
> > Everything I've read indicated that it's only for one string of
> > characters, so I've tried using 2 replaces in a row, but that didn't
> > work either.
> >
> > I just realized I made reference to a piece of script in my last post
> > that was actually from someone else that was trying to help me out.
> > Oops. :-) This part " (Ignoring 'The ', and 'A ' - just like in your
> > example, which I still can't figure out whats wrong with it. Looks nice
> > though. :-) )"
> >
> >
> > They wanted me to try this, but it doesn't work, and I can't figure out
> > why. (I get zero results every time)
> >
> > SELECT title,
> > CASE WHEN SUBSTRING(title,1,4)='The ' THEN SUBSTRING(title,5)
> > WHEN SUBSTRING(title,1,2)='A ' THEN SUBSTRING(title,3)
> > ELSE title END as title1
> > FROM reviews WHERE CASE WHEN SUBSTRING(title,1,4)='The ' THEN
> > SUBSTRING(title,5)
> > WHEN SUBSTRING(title,1,2)='A ' THEN SUBSTRING(title,3)
> > ELSE title END LIKE '$letter%'
> > ORDER BY title1
> >
> > Looks really complicated, but I get what it's trying to do, and seems
> > ok to me, but doesn't work!


I've tried putting 2 replaces for the 2 words I want to ignore, but it
doesn't work! But I actually got the last code I posted to work.... in
phpmyadmin. Now I just have to find out why it doesn't work when I
bring it into my php document.

[Back to original message]