How can I sort an SQLite query ignoring articles ("the", "a", etc.)?

Question

I'm using C# to display a list of movie titles that I am calling from an SQLite database. Currently, I'm using a custom ListBox class that has a function to sort the text stripping the word 'The' from the beginning of every item. However, it doesn't exactly seem to be the simplest way to do it, since it calls from the SQLite database and then sorts. I'd prefer to cut it down to just one step, hopefully sorting straight from the database in my "SELECT" query.

I've done some searching on this, and have found some suggestions, including creating an extra sort-by column in the database. While this is certainly a possibility, I'm wondering if there's any simpler options that don't require inserting almost identical duplicate information (especially if the database becomes larger). I'm pretty new to SQLite, but I've read something about creating a collate function that can be used to create custom ordering. However, I'm not sure if this is appropriate use for it and can't seem to find any help with implementing it in C#.

Was hoping someone might be able to share some guidance. If an extra sorting column is the best way to go, then that is what I shall do.

Answer 1

To avoid inserting duplicate data, what about having two columns: TITLE_PREFIX (usually empty, but sometimes contains "The ", or "A "; no index on this column) and TITLE (contains the title without "The " or "A "; this is the column you create the index on). To display the data, you have to combine TITLE_PREFIX and TITLE. But you just search on TITLE.

Answer 2

Here is the solution:

ORDER BY (CASE 
    WHEN sortTitle LIKE 'the %' THEN substr(sortTitle,5) 
    WHEN sortTitle LIKE 'a %' THEN substr(sortTitle,3) 
    WHEN sortTitle LIKE 'an %' THEN substr(sortTitle,4) 
    ELSE sortTitle END)

Answer 3

You could store each title in 2 parts: title and prefix.

With SQLite you can combine 2 string values via the || operator also known as the concatenate operator.

Here's an example:

SELECT prefix || ' ' || title FROM movies ORDER BY title

You can also use ltrim in case prefix is empty, so you don't have a space at the front:

SELECT ltrim(prefix || ' ' || title) FROM movies ORDER BY title

---

Another alternative is to store the prefix at the end of the title. For example at a lot of movie stores you will see something like:

Three Musketeers, The