I have a table [Users] with FName, LName and City as 3 columns. I want to implement a search algorithm that results from the 3 columns with the AND on top and ORed results appended to it.
For eg:
when I search for Raju Chennai it should result in all the 3 records with 3rd record on top as it is the exact match and other 2 can be followed. How can I achieve this? Please advise.
You will need to look into search weighting algorithms. No-one can write your algorithm for you, that's for you to decide what requires more weighting.
Advanced searches such as this aren't simple. There are so many factors (too many to list) that are up to how much effort you want to put into your search.
A good start will be to use whichever .NET language you're using to split up each word in your search query. Perhaps you'd want to weight the first words moreso than the next, and the next, and so on?
Then decide which is more important for searching. Perhaps the LName is more important than the City? If so, when a match is found in either of the search terms, you could multiply the weight of the column vs the weight of the position of the word in the search query.
Then for multiple columns found, you could add them together? multiply them? Maybe square the LName weight before multiplying? Anything you think will make the weight more accurate (just keep performance in mind).
You can do whatever you want to weight each row, but when that algorithm is done, you can simply ORDER BY Weight to put the most likely row at the top and "hey presto"!
A bit fiddly, but as a starting point, dynamically build up the WHERE clause for the OR, then UNION the results with the exact match. Include a sort column as well, e.g.
SELECT FName, LName, City, 1 AS SortOrder
FROM Users
WHERE
FName = 'Raju' OR
FName = 'Chennai' -- Add the rest of the OR clauses
UNION
SELECT FName, LName, City, 0 AS SortOrder
WHERE FName + ' ' + LName + ' ' + City LIKE '%Raju Chennai%'
ORDER BY SortOrder
I believe, you can make use of full-text search with complex algorithms. Some DBMSes (like MSSQL) have their own implementation, though you can use free and open-source Lucene, for example.
