T-SQL stored procedure for returning to Google-style search queries

Question

Ok, using SQL Server 2008. On my webpage, I have a text box with jQuery-UI autoload enabled.

Now I need a stored procedure to search all the columns of one table (or several joined tables, which I suppose) for a search string coming from an AJAX text field / autocomplete call and return the "suggested" search strings. I use db for AdventureWorks for testing (product table)

So, for example, in the product table there are columns for the product name and product number (among others), and I want to return the suggested search strings based on user input, where they can enter the product name and / or product number.

I am working on a single column that was simple. Any ideas?

Answer 1

I suggest full-text search (MS or Lucene will work). The code below uses MSSQL FTS as its what I am currently using in my application.

Install FTS Search if you haven’t already. If you checked that the service is running. In the management studio, run this to set up a catalog and add a product table; and color / name / product number in the catalog.

USE [AdventureWorks] GO CREATE FULLTEXT CATALOG [ProductsTest]WITH ACCENT_SENSITIVITY = OFF AUTHORIZATION [dbo] GO USE [AdventureWorks] GO CREATE FULLTEXT INDEX ON [Production].[Product] KEY INDEX [PK_Product_ProductID] ON ([ProductsTest]) WITH (CHANGE_TRACKING AUTO) GO USE [AdventureWorks] GO ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Color]) GO USE [AdventureWorks] GO ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Name]) GO USE [AdventureWorks] GO ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([ProductNumber]) GO USE [AdventureWorks] GO ALTER FULLTEXT INDEX ON [Production].[Product] ENABLE GO 

Then you can run queries on all columns at the same time; e.g. Silver (selected as color and name)

 Select * from production.product where contains(*, '"Silver*"') 

Silver * will be found in the query so that you can use it to create results as you type. Keep in mind that Google does this work in real time - if you are looking for a lot of data in order to be able to return data without interrupting user input. I think that people usually use these searches by typing the first letter they are looking for - I agree that there will be spelling errors - you can implement a spellcheck after every place that they may click to handle it. Or save your searches and look at the wrong names and change the code for processing based on matching (or in the FTS using a custom thesaurus.)

Rating will be an interesting problem for the development of any business; Do you find the first result for Mountain Frame - or do you want to weigh them by sales or price? If a user enters more than one text term, you can use FTS to create a ranking based on a search string.

 select aa.rank, bb.* From containstable(production.product, *, '"Mountain" and "Silver*"') aa inner join production.product bb on aa.[key] = bb.productid order by rank desc 

This returns 30 rows; and weights based on user input to determine the first place record. In any case, you most likely want to add a coded rating to customize the results in accordance with your business needs - the rating of the most expensive widget 1 may not be. That's why you are going to store what you were looking for or clicking on people to analyze the results later.

There is a really good language parser for .Net, which converts a google style string query, introduced into the FTS'able language, which gives knowledge of any logical queries that your site uses.

You can also add some wisdom to the crowd functions by checking what the users have, and eventually set off to visit and use the success cards to modify the final offers to actually make them relevant to the user.

As a final suggestion, if this is a commercial site, you can look at Easyask , which is a scary big natural language processor

Answer 2

Using the soundex function will be the easiest way to match for similar “elements” in multiple columns. But the best matching algorithm that will be nearly as fast to implement is the Levenshtein editing distance . Here is a T-SQL implementation wrapped in a function. Use it to match similar search terms.

EDIT Levenshtien sample in action (based on gbn SQL)

Suppose you named your Lvn Levenshtein T-SQL function (just for the sake of brevity), then you can do something like:

 SELECT productname FROM foo WHERE productname LIKE '%myinput%' OR lvn(myinput) < 3 UNION SELECT productnumber FROM foo WHERE productnumber LIKE '%myinput%' OR lvn(myinput) < 3 UNION ... ORDER BY 1 -- one-based column index sort for UNION queries 

Yeah. so simple. Btw, I updated the levenshtein T-SQL link to what makes more sense, and this is an accepted SO answer.

Answer 3

Edit: use UNION to combine individual queries

 SELECT productname FROM foo WHERE productname LIKE '%myinput%' UNION SELECT productnumber FROM foo WHERE productnumber LIKE '%myinput%' UNION ... 

There is no automatic way to scan all columns unless you use dynamic SQL

Answer 4

I created a SQL sample that returns a Google-style search result. You can try this T-SQL. You can also add several search criteria for table columns.

 ALTER PROC [dbo].[USP_GetDoctorLookupList] ( @SearchText varchar(50), @ItemCount int ) AS BEGIN SET @SearchText = RTRIM(@SearchText) + '%' BEGIN SELECT TOP (@ItemCount) * FROM ( SELECT CASE WHEN RTRIM(LTRIM(d.cdocname)) LIKE @SearchText then 1 WHEN RTRIM(LTRIM(d.cdeano)) LIKE @SearchText then 2 END OrderBy, d.docid_PK, d.cdocname, d.cdeano FROM doctor d WHERE (d.cdocname LIKE @SearchText OR d.cdeano LIKE @SearchText ) ) Doc ORDER BY OrderBy, cdocname END END