Search code examples
mysqlselectwords

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.

Solution

  • 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.