Exclude starting words from SELECT query

I'm trying to make a list of movies. First I've got a query that gets all the first letters from the movies:

SELECT DISTINCT LEFT(film_title, 1) FROM ms_films ORDER BY film_title

Second I've got a query that selects all movies that start with the specific letter:

SELECT * FROM ms_films WHERE film_title LIKE 'H%'

Ofcourse quite some movies start with 'The' or the dutch word for it 'De'. Is there any way I can exclude those starting words from开发者_如何转开发 these 2 queries?

Basically a movie like 'The next three days' should be categorised under the N in both queries.

You can use REPLACE to remove them

SELECT * FROM ms_films WHERE REPLACE(film_title, "The", "") LIKE 'H%'

Obviously this removes all the occurrences of The, but it should not matter in your case.

If you need more sophisticated search patterns you can also use REGEXP (or its alias RLIKE) instead of LIKE.

If this is a common task in your application, I would suggest introducing a CHAR(1) column, where the first (actual) letter of the title will be stored. You can create an Index on this column which will be pretty fast, since it's one byte only.