MS SQL

Smart Searches

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Feb 2008

Posts: 22

Reputation: mmxbass is an unknown quantity at this point

Solved Threads: 0

mmxbass

Offline

Newbie Poster

Smart Searches

Oct 28th, 2009

Simply put, I need a better query. I have a table that contains 85000 rows, each row has a TITLE and an ABSTRACT (they are academic papers).

What I need is to do a realistic SMART search in my query.
Right now I'm doing

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT * FROM PsPapers WHERE Title LIKE '%MySearchString%'
SELECT * FROM PsPapers WHERE Title LIKE '%MySearchString%'

which is, as you can see, a fairly retarded search system. This has several problems:

If the search is "othello essays" then my query only gives you ones where the title contains "othello essays" even though something like "essays on othello" I would also want to be returned because the title is OBVIOUSLY on point.
My query doesn't use the abstracts right now, the reason is that the abstracts contain alot of crap and should be WEIGHTED MUCH LESS than titles. I would want title matches to list HIGHER than abstract matches.
There is no real cleverness or weighting here. The older cgi system I'm trying to replace uses code taken from an actual search engine so I need to at least ATTEMPT to do a smart search.

Any thoughts on a better query?

•

Join Date: Feb 2009

Posts: 3,364

Reputation: sknake has much to be proud of

Solved Threads: 606

sknake

Offline

.NET Enthusiast

Oct 29th, 2009

Given the search term "othello essays" on the application front end you need to split up the search terms. I think full text indexing offers more options for weighting search results. You may want to take a look at it. Here is one way to help make it better, but only slightly:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
IF OBJECT_ID('tempdb..#PsPapers', 'U') IS NOT NULL DROP TABLE #PsPapers
IF OBJECT_ID('tempdb..#Results', 'U') IS NOT NULL DROP TABLE #Results
 
CREATE TABLE #PsPapers
(
  PaperId int identity(1000, 1) PRIMARY KEY,
  Title varchar(100),
  Body varchar(8000)
)
 
 
 
Declare @Search varchar(100), @s1 varchar(100), @s2 varchar(100)
SET @Search = 'othello essays'
SET @s1 = '%othello%'
SET @s2 = '%essays%'
 
INSERT INTO #PsPapers (Title, Body) Values ('othello essays', '')
INSERT INTO #PsPapers (Title, Body) Values ('essays on othello', '')
INSERT INTO #PsPapers (Title, Body) Values ('essays on XXothello', '')
INSERT INTO #PsPapers (Title, Body) Values ('notes on othello', '')
INSERT INTO #PsPapers (Title, Body) Values ('essays on chocolate', '')
 
SELECT *, Cast(1 AS int) AS Relevance
INTO #Results
FROM #PsPapers
WHERE Title = @Search
 
Union ALL
 
SELECT *, Cast(2 AS int)
FROM #PsPapers
WHERE Title LIKE @s1 AND Title LIKE @s2
 
Union ALL
 
SELECT *, Cast(3 AS int)
FROM #PsPapers
WHERE Title LIKE @s1 
 
Union ALL
 
SELECT *, Cast(3 AS int)
FROM #PsPapers
WHERE Title LIKE @s2
 
SELECT PaperId, Title, Body, Min(Relevance) AS Relevance
FROM #Results
GROUP BY PaperId, Title, Body
ORDER BY Relevance ASC
IF OBJECT_ID('tempdb..#PsPapers', 'U') IS NOT NULL DROP TABLE #PsPapers
IF OBJECT_ID('tempdb..#Results', 'U') IS NOT NULL DROP TABLE #Results

Create Table #PsPapers
(
  PaperId int identity(1000, 1) PRIMARY KEY,
  Title varchar(100),
  Body varchar(8000)
)



Declare @Search varchar(100), @s1 varchar(100), @s2 varchar(100)
Set @Search = 'othello essays'
Set @s1 = '%othello%'
Set @s2 = '%essays%'

Insert Into #PsPapers (Title, Body) Values ('othello essays', '')
Insert Into #PsPapers (Title, Body) Values ('essays on othello', '')
Insert Into #PsPapers (Title, Body) Values ('essays on XXothello', '')
Insert Into #PsPapers (Title, Body) Values ('notes on othello', '')
Insert Into #PsPapers (Title, Body) Values ('essays on chocolate', '')

Select *, Cast(1 as int) as Relevance
Into #Results
From #PsPapers
Where Title = @Search

Union All

Select *, Cast(2 as int)
From #PsPapers
Where Title Like @s1 and Title Like @s2

Union All

Select *, Cast(3 as int)
From #PsPapers
Where Title Like @s1 

Union All

Select *, Cast(3 as int)
From #PsPapers
Where Title Like @s2

Select PaperId, Title, Body, Min(Relevance) As Relevance
From #Results
Group By PaperId, Title, Body
Order By Relevance Asc

Results in:

Help with Code Tags

text Syntax (Toggle Plain Text)

PaperId     Title                     Body       Relevance
----------- ------------------------- ---------- -----------
1000        othello essays                       1
1001        essays on othello                    2
1002        essays on XXothello                  2
1003        notes on othello                     3
1004        essays on chocolate                  3

You will need to break the search input string on word boundaries and search for each word and weight the results.

Last edited by sknake; Oct 29th, 2009 at 3:37 am.

Scott Knake
Custom Software Development
Apex Software, Inc.