June 18, 2012 at 5:04 am
Hello All,
I need your help to solve one query. The case is we have a table for storing information regarding Cars, like Year, Make of cars Color of that cars. And we would like to create a search where user can inputs any keywords in search box and the list should be populated with most possible combinations and also in the most matching in order should appear first.
Example if User inputs “BMW Black” then all BMW cars and also those who are of black color should come but the order should BMW first followed by other cars.
2) if search is “Black Audi”: then all Black cars should be listed first followed by Audi cars.
Can anyone help us to create such query. Thanks in advance.
Thank You
Yatish
June 18, 2012 at 5:49 am
Full-text search.
You will need to create the view which will hold the concatenated value of columns you want to search by. Then create Full-Text index on this column and you will be able to use Full-text search to do something very close to what you want.
June 18, 2012 at 5:57 am
Hello,
Can you please let me know any sample query if how I can implement this.
Thank you
yatish
June 18, 2012 at 6:12 am
I'm not going to write the sample query as there are a lot of them if you google.
I can give a bit more detailed steps of what you will need to do to try it out:
1. Enable Full-Text search in your database and create Full_text catalog
2. Create an view which will have a key to the records you want to display and concatenated value on which you would like to perform the search, something like:
CREATE VIEW v_CarModelSearch
AS
SELECT ModelId
,MakerName + ' ' + Color + cast(YearBuild as VARCHAR) AS SearchText
FROM dbo.Models.
3. You must create a unique clustered index for the above view (that why some key columns is required there)
4. Create Full Text index in the Full Text catalog for this view on your [SearchText] column
Now you will be able to use Full-Text search methods (CONTAINS will allow to do simple searches, CONTAINSTABLE- will give you rank of the match)
Also, you may want to clear out noise words out of STOPLISTS.
Here the link to get you more informed:
http://msdn.microsoft.com/en-us/library/cc721269(v=sql.100).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply