3

Pretty basic question, but how can I get the containstable search to do an actual 'contains' search, rather than a 'starts with' one?

For example, from the examples in the documentation:

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));  
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);  
INSERT Flags VALUES ('France', 'Blue and White and Red');  
INSERT Flags VALUES ('Italy', 'Green and White and Red');  
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');  
SELECT * FROM Flags;  
GO  

CREATE FULLTEXT CATALOG TestFTCat;  
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;  
GO   

SELECT * FROM Flags;  
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;  
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;  

Works fine, and so does:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"Gree*"') ORDER BY RANK DESC;  

But this does not:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen"') ORDER BY RANK DESC;  
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen*"') ORDER BY RANK DESC;

Should I be using a different query, or is what I'm looking for not possible?

5

This is, I think, a common misconception when it comes to full text search in SQL Server.

Full Text Search enables searching for entire words as well as a number of other things. From the CONTAINS documentation

CONTAINS can search for:

  • A word or phrase.

  • The prefix of a word or phrase.

  • A word near another word.

  • A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

  • A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

So just like with normal indexes on text strings, you can do prefix searching.

Neither full text search, nor normal SQL Server indexes, support "postfix" or "contains" searching within a word or text string. So no leading wildcards, essentially.

Note that the same limitations apply to CONTAINSTABLE:

CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.

I imagine these limitations exist because of the way indexes are logically stored in SQL Server - as b-trees, where it's very easy to "jump" or "seek" to the different parts of an index based on the start of the data in the indexed column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.