0

This is a longer one, so please bear with me :-)

I'm in the situation to advance the search for our intranet shop. The current solution is working OKish, but there are some drawbacks the way it is implemented.

We are using ASP.NET and SQL Server 2008 R2, and the search mostly runs with CONTAINSTABLE. The problem we run into is that some users need to find items that have specific technical/physical details in the description. Some examples for search queries are

SomeItem 1/2"
SomeotherItem 30°C
SomeThirdItem 3,8V

As an example Item descriptions I'm trying to find with the first search term, the items look mostly like this

SomeItem 1/2"x3/8" additionalTextblabla
SomeVendor SomeItem 1/2"x3/8" additionalTextblabla

I'm using Irony to convert the search query into search criteria for CONTAINSTABLE. I even support stuff like exclusions (with -) in front of the search terms and wildcards at the end of search terms.

Now as far as I understand it, when updating the full text index, terms like 1/2" are not indexed, and therefore CONTAINSTABLE does not find it when looking for it as a condition. I'm also also running into the problems with " and ' used in a search because it causes syntax errors in SQL level and therefore I have to discard them even before the search hits the database.

Example: SomeItem 1/2" is currently transformed into the search criteria ("SomeItem" AND "1/2") and returns 0 result because the FullTextIndex counts / as punctuation and does not index that part of the items description I'm trying to find (at least as far I understand it).

How do I solve this problem ? Can this be done with CONTAINSTABLE or do I need to do something entirely different ? I ran searches on a couple of our vendors own shops, which seem to have 0 problems with this sort of queries.

The current "workaround" is that if the search return no results, I run the same search again with FREETEXTTABLE instead of CONTAINSTABLE, which mostly finds the items, but a lot of unrelated stuff as well.

We are about to test our code on SQL Server 2014 soon, does this make a difference on how the full text index works ?

Best regards

-Dave

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dave
  • 81
  • 5
  • If you are having issues with single and double quotes causing syntax errors it is because you are not parameterizing your queries. I wouldn't even attempt to roll my own extended search like what you are trying. I would use a search tool like Lucene instead which already does all the hard parts. – Sean Lange Aug 23 '17 at 17:00
  • We are using Stored Procedures for our search queries, so this is not an issue. The Problem I have is that I don't know a way to get the Full text search to not ignore terms like 1/2" or 3,8V. In my mind it is hard to believe that this can't be done with SQLServer – Dave Aug 24 '17 at 07:30
  • Stored procedures do not prevent sql injection. Are you building up a string and executing it? I think you are trying to tackle this the wrong way. You should fix the problem, not the symptom. Make you search so it finds 1/2" correctly, not ignore it. I personally can't stand searches that ignore important information like that. – Sean Lange Aug 24 '17 at 13:33

0 Answers0