CODEWITHSUNDEEP
sql-server
CodingUnderDuress
CodingUnderDuress

Reputation: 103

Transact SQL CONTAINS not returning match for identical strings with a wildcard

I'm encountering an issue on SQL Server 2008r2 where use of the CONTAINS function is not returning a match for completely identical strings. I'm pretty sure I'm misunderstanding something about CONTAINS.

I've set up the following test case to replicate it.

CREATE TABLE texttest
(
    loldex INT PRIMARY KEY IDENTITY,        
    testcolumn NVARCHAR(255)
)

GO

INSERT INTO texttest
VALUES('2ND ACADEMY OF NATURAL SCIENCES')

GO

CREATE UNIQUE INDEX ui_loldex ON [texttest](loldex);
CREATE FULLTEXT INDEX ON [texttest]([testcolumn])
KEY INDEX ui_loldex

CREATE FULLTEXT CATALOG ft AS DEFAULT;

GO

Then I run the following query:

SELECT [testcolumn]
FROM [texttest]
WHERE CONTAINS(testcolumn, '"2ND ACADEMY OF NATURAL SCIENCES*"')

And it returns nothing.

However, if I run:

SELECT [testcolumn]
FROM [texttest]
WHERE CONTAINS(testcolumn, '"2ND ACADEMY*"')

It returns the row.

If I use any more of the characters in the string after the Y with the wildcard, I get nothing.

If I remove the wildcard, I get the row from the identical string. However, I've tried the wildcard with other strings and it's worked fine. From what I understand, the wildcard stands for 0 or more additional characters, so I don't understand why it doesn't work in this case.

Any idea what I'm missing here?

Upvotes: 2

Views: 404

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

This is because of is noise word and it will not select the results if you search by stopwords. You can see all stop words :

select ssw.stopword, slg.name
  from sys.fulltext_system_stopwords ssw
  join sys.fulltext_languages slg on slg.lcid = ssw.language_id
where slg.name = 'English'

You can turn it off for your table:

ALTER FULLTEXT INDEX ON [texttest] SET STOPLIST = OFF

You can manage stoplists and stopwords: https://technet.microsoft.com/en-us/library/ms142551(v=sql.110).aspx

For example:

stopword    name
must        English
my          English
never       English
no          English
now         English
of          English
on          English
only        English
or          English
other       English
our         English
out         English
over        English
re          English

Upvotes: 2

Hell Boy
Hell Boy

Reputation: 981

Use freetext function to search for whole sentence in your case:

SELECT [testcolumn]
FROM [texttest]
WHERE freetext(testcolumn,'"2ND ACADEMY OF NATURAL SCIENCES*"')

Output:

testcolumn
2ND ACADEMY OF NATURAL SCIENCES

Upvotes: 0

Related Questions