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:
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:
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.
Reputation Points: 1749
Solved Threads: 735
Senior Poster
Offline 3,948 posts
since Feb 2009
Unsolved 
if it is not possible, try the ...table repair application 
