15

I have a Products table in a SQL Server database and I am having to troubleshoot a legacy stored procedure that uses Full-Text indexing. For our purposes here, lets suppose that the Products table has two fields ID, Keywords. And the Keywords field is populated with the following:

ROLAND SA-300 This Roland SA-300 is in MINT condition!

When I run the following statement, I am able to retrieve the record:

SELECT * FROM Products WHERE Keywords LIKE '%SA-300%'

However, when I run any of the following statements I get zero results:

SELECT * FROM Products WHERE CONTAINS(Keywords, ' SA-300 ')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA-300')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"SA-300"')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"*SA-300*"')

But I know that the CONTAINS() function is working because I get results when I run any of these:

SELECT * FROM Products WHERE CONTAINS(Keywords, ' Roland ')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'Roland')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"Roland"')
SELECT * FROM Products WHERE CONTAINS(Keywords, '"*Roland*"')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'Roland')

I need to figure out why the CONTAINS() function isn't working on the 'SA-300' term. I am new to Full-text indexes, so any help is appreciated.

Maksym Gontar
  • 22,765
  • 10
  • 78
  • 114
jessegavin
  • 74,067
  • 28
  • 136
  • 164

5 Answers5

16

Two thoughts:

(1) The hyphen might be treated as a word break What do these return?

SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA 300')
SELECT * FROM Products WHERE CONTAINS(Keywords, 'SA300')

See this other question.

(2) Have you tried rebuilding your full-text index? It is possible it is out of date.

Community
  • 1
  • 1
JohnFx
  • 34,542
  • 18
  • 104
  • 162
4

Turns out that I needed to re-build my full-text index. Thanks for the tips @MichaelGG and @JohnFx

jessegavin
  • 74,067
  • 28
  • 136
  • 164
2

Ah! Thank you @GregD. We had a search that wasn't finding the string "this never ends" when looking for "this*" or "never*". Turns out both words are in noiseENG.txt.

It doesn't look like it's possible to turn this off on a per-query basis, but based on this: SQL 2008: Turn off Stop Words for Full Text Search Query

I ran

ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = OFF

and we started getting the results we wanted.

Community
  • 1
  • 1
Maxcelcat
  • 151
  • 3
0

Have you had a look at your noise words file? On my machine it's located here c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

One way that you could test this is by editing your particular noise file with just a single space so that it indexes everything and try your query again. I would "test" this in a test environment and not in production.

GregD
  • 6,860
  • 5
  • 34
  • 61
-1

Can you try 

SELECT * FROM Products WHERE CONTAINS(Keywords, ' "SA-300" ')

I saw an example of what you are trying to do on msdn.

ScArcher2
  • 85,501
  • 44
  • 121
  • 160