website searching - Is there a fulltext and match/against tool in MS SQL?

I may be a little off but I know in mySql you can make a field as Full Text and searchable.

then using the  MATCH/AGAINST tools it will do a basic search of the fields "title" and "body" from the below example.
the against column is not literal so something like "Green Chamo Shirt" will return ranked results in title and body where any one of "green", "chamo","shirt" show up giving higher rankings to all 3 words showing up.

example of mysql query:

SELECT * FROM articles
        WHERE MATCH(title, body) AGAINST ('Green Chamo Shirts')

could return products with: (ordered by ranking of relevance)

Green Chamo Shirts
Shirts with Chamo patterns
Green Shirts
Green shoes
Shirts


etc.


The million dollar question does ms sql have the same or similar function?

I am new to MS SQL so I am still learning and was planning on making that change in the search functions myself once/if I figure it out.

the only other way is to break each search term into tokens and create a on the fly query based on each word.  Seems like a lot of hits to the data base.  or perhaps a stored procedure.

What I want to do is simple search text that is contained in my descriptions,product title,possibly even the SEO keywords etc for a given product so that I get relevant search results.  Right now it only looks at the product variant product title only.  Which I know I need to change myself.

anyway if anyone has any direction I would greatly appreciate it.

Go here to download the sql script:
http://nopcommerce.codeplex.com/workitem/7651

wow!  that is great.
I was working last night on enabling full text and starting the daunting task of hunting down the code.

but now you have saved me hours and hours of work.

Thanks a lot.  I really appreciate that you took the time to respond.


I will note that when you are using a local server for testing and using sql express it is possible that fulltext is not enabled.  Especially if you take a back up from a sql script etc.   I created the initial test site on a hosting site then copied it local.  The method I used was to do a sql script back up (created a sql script that I could run somewhere else to replicate).  Instead you need to use the database backup utility that create a .bak file.  Then you will have the "storage" folder that allows you to do the fulltexting.  Spent way to much time scratching my head on that one.

By default sql server 2008 and newly released R2 should have it all enabled.  Softsyshosting is now using sql server 2008 r2.

Funny how complicated fulltext searching is in sql server.  setting it up in free mysql is a snap.

Mike

ok one thing I added to the dbo.Nop_ProductLoadAllPaged2 script.

IF (@Keywords <> '')
  BEGIN
  
   --little trick to remove all extra spaces.  REGEX is probably better here.
  
   WHILE charindex(@Keywords,'  ')>0
    BEGIN
    SET @Keywords=replace(@Keywords,'  ',' ')
    END
   set @Keywords=RTRIM(ltrim(@Keywords))
   set @Keywords=replace(@Keywords,' ', '" or "')
   SET @Keywords ='"*' + rtrim(ltrim(@Keywords)) + '*"'
  END

so "9.6 volt battery packs"  is being converted to:
"*9.6*" or "*volt*" or "*battery*" or "*packs*"

now to strip off the "s" on plural words.  I think there is a synonym tool that will do something like that.


by doing this each word entered into the text box is searched against not as a complete sentence.
not sure if this is really doing what MySQL does but it is getting me results that I want to see for now

This issue appears that when a customer types in a phrase "9.6 battery packs" that even in full text it is looking for that phrase.  Instead it should be looking for "9.6" or "battery" or "packs" and then creating a ranking based on results.
results of all 3 matching show highest in the list.  those that show 1 keyword or partial show at the bottom.

Now if this is just a setting that I need to set please let me know.  But other wise I am forcing it with the "or" keyword but my fear with that is there is no ranking.  

Mike

so what is needed is this:

SELECT DISTINCT ftt.RANK,p.ProductID,p.Name,p.ShortDescription
  FROM
  FREETEXTTABLE(Nop_Product ,(Name,FullDescription),N'9.6 lipo battery packs') ftt
  INNER JOIN Nop_Product p ON ftt.[KEY] = p.ProductID
  ORDER BY ftt.RANK DESC
(of course the above is limited to only the product table but there is a way around it below I think)

This returns exactly what all of us are looking for in a search function.  It ranks it by relevance etc.

But the search function stored procedure using the FREETEXTTABLE looks a little difficult.  You would have to create temp table for the normal results but include all the fields you want to search using FREETEXTTABLE.  Then run FREETEXTTABLE against the new table and columns.

FREETEXTTABLE only allows one table to be searched so you can't simply do multiple searches or you would get conflicting rankings.

i.e.

SELECT DISTINCT p.ProductID,p.Name,p.ShortDescription,+ all needed columns for the returned query
  FROM
  FREETEXTTABLE(temptable ,(Name,FullDescription,VarianceDescription,categorydescr,etc,etc),N'9.6 lipo battery packs') ftt
  INNER JOIN Nop_Product p ON ftt.[KEY] = p.ProductID
  ORDER BY ftt.RANK DESC

You would only run the above when doing a KEYWORD search from the site.  not when doing normal site navigation.  So if/else clause on Keyword!="" would do the trick as to when to use the above.

Has anyone done the above already?

The CONTAINS keyword does help but does no ranking so you don't get results that are as clean as FREETEXTTABLE.

shoot - wont work  Guess you can't really do multiple tables with freetexttable.

still stick with the original solution.  Not ordered but at least they are relevant to what they are looking for.

Mike