1

Tinkering with a plugin for using Full Text Search on SQL Azure, and I'm having a problem with noise words, especially when using CONTAINSTABLE.

Logs of real-world search queries for our sites indicate that I need to deal with the noise words one way or the other. What I would like to do is set 'transform noise words' as referenced here but sp_configure appears to not be allowed.

Is there another way to enable this feature, or are their other options I should be considering to deal with this problem?

Thanks.

Community
  • 1
  • 1
Patrick Bates
  • 26
  • 3
  • Did you try creating new stoplist and build index using that? You can add noise words to that – Sirisha Chamarthi May 22 '15 at 03:35
  • Thanks for that. Didn't solve the problem, but it gave me a workaround. After duplicating the system stoplist I was able to extract a distinct list of the English words. I'm using those in an array_diff to remove them from queries before submitting them to SQL. Still need a way to do it properly with the default list, though, so it will support all languages easily. – Patrick Bates May 22 '15 at 18:00

2 Answers2

0

Changing configuration options for full-text service is not available in Azure SQL Database as mentioned here (see limitations section): http://azure.microsoft.com/blog/2015/04/30/full-text-search-is-now-available-for-preview-in-azure-sql-database/. A database scoped configuration option similar to instance level configuration with sp_configure is still in the works for Azure SQL Database.

MihaelaBlendea
  • 243
  • 1
  • 4
0

A possible workaround is to use sys.dm_fts_parser to filter out the noise words from your search term. For the search term

The quick brown fox jumps over the lazy dog

And then use this

SELECT STUFF(
(
    SELECT ' ' + display_term FROM sys.dm_fts_parser (' "The quick brown fox jumps over the lazy dog" ', 1033, 0, 0)
    WHERE special_term<>'Noise Word'
    FOR XML PATH('')
), 1,1,'');

Gives you

quick brown fox jumps lazy dog

Of course it would be better if the config option was supported in Azure...

Matt McCabe
  • 2,986
  • 2
  • 22
  • 29