SQL Server 2008 - Search Query

Question

Hello, I am not a SQL Expert. I’m trying to elegantly solve a query problem that others have had to have had. Surprisingly, Google is not returning anything that is helping. Basically, my application has a “search” box. This search field will allow a user to search for customers in the system. I have a table called “Customer” in my SQL Server 2008 database. This table is defined as follows:

Customer

• UserName (nvarchar)

• FirstName (nvarchar)

• LastName (nvarchar)

As you can imagine, my users will enter queries of varying cases and probably mis-spell the customer’s names regularly. How do I query my customer table and return the 25 results that are closest to their query? I have no idea how to do this ranking and consider the three fields listed in my table.

Thank you!

Answer 1

I would suggest full-text search. Full-text search will provide plenty of options for dealing with some name variants and can rank the "closeness" of the results using CONTAINSTABLE. If you find that full-text search is not sufficient, you might consider a third-party indexing tool like Lucene.

Answer 2

The case issue you can solve easy by setting your table collation to be case insensitive

The misspelling not sure how to handle but have a look at the full text search capabilities of sql server..

Answer 3

You might want to try using SOUNDEX or DIFFERENCE as an alternative to full text search.

SELECT TOP 25 UserName, FirstName, LastName
FROM Customer
WHERE DIFFERENCE( UserName, @SearchValue ) > 2
ORDER BY DIFFERENCE( UserName, @SearchValue ), UserName