Consider the following table:

People

• FirstName nvarchar(50)
• LastName nvarchar(50)

Let's assume for the moment that this table has a full-text index on it for both columns.

Let's suppose that I wanted to find all of the people named "John Smith" in this table. The following query seems like a perfectly rational way to accomplish this:

SELECT * from People p
INNER JOIN CONTAINSTABLE(People,*,'"John*" AND "Smith*"')

Unfortunately, this will return no results, assuming that there is no record in the People table that contains both "John" and "Smith" in either the FirstName or LastName columns. It will not match a record with "John" in the FirstName column, and "Smith" in the LastName column, or vice-versa.

My question is this: How does one accomplish what I'm trying to do above? Please consider that the example above is simplified. The real table I'm working with has ten columns and the input I'm receiving is a single string which is split up based on standard word breakers (space, dash, etc.)