CODEWITHSUNDEEP
sqlsql-server-2008
user208662
user208662

Reputation: 10997

SQL Server 2008 - Search Query

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

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!

Upvotes: 3

Views: 1038

Answers (3)

tvanfosson
tvanfosson

Reputation: 532445

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

Upvotes: 2

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195962

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..

Upvotes: 0

Thomas
Thomas

Reputation: 64635

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.

Upvotes: 4

Related Questions