need help, to Search from database

Hello Friends,
Actually i am Sticken with a very peculiar problem, related to search

Actually i have a database with a column "Description" which has a long strings of 1000 characters describing about the produts of the company

for E.g. "We are manufcaturers of Plastic Raw material, platic containers, Elctrical items Etc.............." & so on.

so, the problem is that i want when some person search for particular item, i want to show them in the relevance order

for e.g.
somebody searches for "plastic container & raw material"
so i want the record to search for "plastic", "plastic container", "raw material", & "plastic raw material" and single words like "plastic", "Container", "Raw", "Material"

after the searching i want to put them in relevant order
-such as the string which contain the actual term should be at top
-String containing maximum number of keywords should be placed afetr that
-string contining all the single letters should be placed at last

Please help me

i can configure my database as per the requirement,
i am sticken at this point of time.

Hoping to get some better response from you folks

"Thanks" IN ADVANCE

from
Vikram Sachdeva

You will need a stored procedure to do that.

You need to create a temp table, with a column for the PK from your descriptions table, plus a rank number.

Your selects will ned to do something like...

select ID from descriptions WHERE description LIKE '%' + searchstring + '%', [rank] = 100 INTO #temptable

and then split your search string into word parts, and do further selects with lesser ranks depending on how well they meet your WHERE clause...

e.g. select .... where descirption like 'word1' and description like 'word 2'

so you want to end up with a table like....

|DescID|Rank|
| 51 | 100|
| 21 | 50 |
| 652 | 80 |
| 94 | 65 |
| 51 | 100|

Then you can join that back to your descriptions, order by rank, and bobs your uncle. If I wasn't so busy, i'd write a proper example, but I don't really have the time.

You could also try googling for SQL Full Text Search