Search for all combinations of first, middle and last name

Question

In my database there is a table called author, which 4 columns:

• authorID
• firstName
• middleName
• lastName

A user, for example, is searching for Edgar Allan Poe. In our table Edgar Allan Poe is saved as: firstName- Edgar, middleName - Allan and lastName - Poe. This query is pretty straightforward. But how to write a query that matches not only Edgar Allan Poe, but also Poe Allan Edgar, Edgar Poe, Allan Poe, Edgar Allan, Allan Edgar Poe without writing all these possible combinations on our own? Also when the user is searching, he/she is searching as 'Edgar Allan Poe' or 'Poe Allan Edgar' altogether, not in a separate fields.

Answer 1

In any database, you can do something like this:

where firstName in ('Edgar', 'Allan', 'Poe') and
      middleName in ('Edgar', 'Allan', 'Poe') and
      middleName <> firstName and
      lastname in ('Edgar', 'Allan', 'Poe') and
      lastname not in (firstname, middleName)

This is actually pretty easy to extend to more names, if you like -- assuming the names are distinct as in your example (if you want to allow authors with duplicated names, just remove lines 3 and 5 from the above query).

However, depending on your database, you might want to use regular expressions or full text search instead.