Querying for names

Question

I have a table (say "Demographics") on SQL Server. In this table are the following columns "LastName", "FirstName", "MiddleName", "Prefix" , "Suffix".

I have one textbox to search for them on a webpage. My question: Whats a good design strategy for queries where the user could be searching for various combinations of names including last names that have spaces

e.g. "Smith James Jr." . Our customers store Jr. sometimes in suffix and sometimes just tack it to lastname. Sometimes Smith James Jr could be the entire last name.

Answer 1

You could start by by prioritising the mossible combinations (firstname + lastname, middlename + lastname, lastname + suffix, etc.). Then, for each combination, you could write a SELECT statement which returns a

SELECT 50 AS score, tableId
FROM SearchTable
WHERE '%' + FirstName + ' ' + LastName + '%' LIKE @searchphrase

UNION

SELECT 40 AS score, tableId
FROM SearchTable
WHERE '%' + MiddleName + ' ' + LastName + '%' LIKE @searchphrase

ORDER BY score DESC

For each statement, you could manipulate the score at a finer level (for example +5 if it's a full match on LastName).

To filter out spaces and other non-searchable characters, I suggest you create a user-defined function.

Good luck!

Answer 2

Noticed you are working on MS SQL 2005.
Have you looked at SQL Server 2005 Full-Text Search ?

Nice article about searching I'd rcommend: Understanding Full Text Search in SQL Server 2005