Re: sql query to filter out prepositions ie. 'the, a ...'

Barnoit wrote:
> I'm trying to write a query that does a search on the first letter in
> a column. That part is ok, ie:
>
> select book_title
> from books
> where UPPER(substr(book_title,1,1)) = 'A';
>
> But some book titles might be 'The Lord of the Rings' or 'A
> Nightmare...', so I'd only want the query to do the search on the
> actual name. I would have a reasonably short list of those
> prepositions I'd want to trim off.

For a variety of reasons, performance being the main one, I'd suggest you create a search column with the filtered titles. Then populate it and filter out the words you've chosen not to use. And make it case insensitive by putting all character in the same case (upper or lower, your choice, again). You can take out other characters like commas and white space too.

so The Lord of the Rings might become LORD RINGS or LORDRINGS and The Ring becomes RING
and The War of the Roses might be WARROSES

You get the idea. then search on the search column and display the title column. Making the list of what to trim is your decision.

HTH

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost