How do I select titles from a mysql DB that begin with a specific letter?

Question

I need to make an alphabetical listing of movie titles, so I need to show only items that begin with a chosen letter. To make this slightly more complicated, many titles start with "the" or "a", which needs to be ignored.

How would the mysql query look to achieve such a task?

I could simply have an extra column with the search letter stored for every title, since its quite easy to do with php, but Im hoping for a cleaner solution than that.

Answer 1

SELECT  * FROM blah WHERE TITLE LIKE 'a%';

or

SELECT * FROM blah WHERE SUBSTRING(TITLE, 1,1)='a';

Or, taking it further, to cover the ignoreing:

SELECT * FROM blah WHERE 
IF(SUBSTRING(TITLE, 1, 2)='a ',
  SUBSTRING(TITLE, 3, 1), 
  IF(SUBSTRING(TITLE, 1, 4) = 'the ', 
    SUBSTRING(title, 5, 1), SUBSTRING(TITLE, 1,1)
  )
)=='a';

You should be able to use the same if blocks for getting the data in other cases as well.

EDIT: Credit where its due - the regex option from Quassnoi is much more concise - be interested to see how performance compares.

However, it will probably perform better with a dedicated field for this.

Answer 2

To select only the titles starting with a chosen letter, you can do something like

select * from movies where title like 'a%'

Answer 3

This will handle the "starts with" requirement:

SELECT title FROM table WHERE title LIKE "D%"

To deal with the movie titles which start with "a" or "the" etc. is it possible to adjust the way these are entered into the database. eg. "Movie, A" instead of "A Movie"

Answer 4

SET @letter = 'A';

SELECT  title
FROM    movies
WHERE   title REGEXP CONCAT('^(the |a )?', @letter);

This, however, will not use an index on title if any.

For the index to be used, you will need to do the following:

SET @letter = 'A';

SELECT  title
FROM    movies
WHERE   title LIKE CONCAT('the ', @letter, '%')
        OR title LIKE CONCAT('a ', @letter, '%')
        OR title LIKE CONCAT(@letter, '%')