Database Search minus "The" prefix

could someone please point me in the right direction, I currently have a searchable database and ran into the problem of searching by title.

If the title begins with "The" then obviously the title will be in the 'T' section, what is a good way to avoid "The开发者_StackOverflow" being searched ? Should i concat two fields to display the title but search by only the second title ignoring the prefix. or is there another way to do this? Advice or direction would be great. thanks.

A few choices:

a) Store the title in "Library" format, which means you process the title and store it as

Scarlet Pimpernel, The
Tale of Two Cities, A

b) Store the original unchanged title for display purposes, and add a new "library_title" field to store the processed version from a).

c) Add a new field to store the articles, and the bare title in title field. For display, you'd concatenate the two fields, for searching you'd just look in the title field.

I believe the best approach is to use full-text search, with 'the' in the stopwords list. That would solve the search problem (i.e., 'the' on search phrases would be ignored).

However, if you are ordering the results by title, a title starting with 'The' would still be sorted, "in the 'T' section", as you put it. To solve that, there are several possible approaches. Here are some of them:

• Separating the fields, the way you said on the quesiton
• Having a separate field with the number of chars to be ignored from the beginning when sorting
• Replacing initial 'The's for sorting

Among others...

If you are using mysql, you could use a str_replace function to remove "The" from your query, or if you are using PHP or Ruby or another language you can just sanitize your query before sending to the database server.

Create three columns in the database

1) TitlePrefix 2) Title 3) TitlePostfix

Code such that you have 4 methods like

searchTitleOnly(testToSearch)  // search only title column
searchTitleWithPrefixAndPostfix(testToSearch)//concat all the three columns and search 
searchTitlePrefix(testToSearch) // search title prefix only
searchTitlePostfix(testToSearch) // search title postfix only

Try looking into some sql functions like LTRIM, RTRIM etc and use these functions on a temp column which has exact same data. Modify the data by using LTRIM, RTRIM by dropping whichever words u please. Then perform the search on the modified column and return the entire row as the result!