Full Text Search "Contains" question

Question

Full Text Search "Contains" question

mpdillon

Hall of Fame

Points: 3566
More actions
May 24, 2011 at 8:10 am

#389578

I am new to Full Text Searches (MS SQL 2008). I have question about CONTAINS. This clause is working more like an exact match rather than a LIKE '%variable%'. For example.
If the WHERE clause CONTAINS(*,'marker') the search will not return fields that contain the word "markers". However, CONTAINS(*,'markers') does return "markers".
Can someone please offer an explanation or point me to an article that explains why? Is there a way to get a match between marker and markers using CONTAINS?
Also, it is just not the one term "marker". I get the same behavior with "pencil". Contains(*,"pencil") does not return Pencils?
I also tried Contains(*,'*marker*') but markers are still not returned.
Thanks,
pat

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 1

I may be misunderstanding the CONTAINS predicate, but it seems to me that it searches for a whole word, not part of a word.

BOL
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 thesaurus (for example, the word metal can have synonyms such as aluminum and steel).

I guess the thing to check is to see if "markers" gets you the singular version "marker" and do the same test for "pencils".

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Full Text Search is very different to using LIKE and if you need pattern matching within a string, you're using the wrong tool - it's based around language and full words.

However, in terms of simple pluralisation, you want to look at using FORMSOF(INFLECTIONAL...

e.g.

CONTAINS (*, 'FORMSOF(INFLECTIONAL, "marker")')

It's not perfect, but it will catch most basic stuff. More info here:

http://msdn.microsoft.com/en-us/library/ms142566.aspx

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

Oh, Howard, that's good info. Thanks for the link! (not the OP @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

mpdillon Hall of Fame Points: 3566 More actions · Answer 4

Guys,

Thanks for the info. I do appreciate the help since I know so little.

pat

HowardW One Orange Chip Points: 29109 More actions · Answer 5

HowardW

One Orange Chip

Points: 29109

May 24, 2011 at 9:26 am

#1329158

Glad you found it useful. 🙂