Confusing CONTAINS behavior

I'm experimenting with the CONTAINS operation and I've come across some
confusing or inconsistent results. The following results are exactly the
same if the search terms are enclosed in double quotes so that they are
treated as a phrase.
I'm searching a Books table for books whose Author column contains "cs".
The following CONTAINS statements in the WHERE clause result in books by
"Lewis, C.S." being returned:
CONTAINS( Author, 'cs' )
CONTAINS( Author, 'CS' )
CONTAINS( Author, 'C.S.' )
The fact that "cs" matches an author containing "C.S." indicates that both
case and punctuation are ignored. However, the following CONTAINS statement
does not return any results for "Lewis, C.S.":
CONTAINS( Author, 'c.s.' )
This puzzles me. It seems as though either punctuation is ignored or case
is ignored, but not both. What kind of behavior is that? Is this by design
?Greg Smalter wrote:
> I'm experimenting with the CONTAINS operation and I've come across
> some confusing or inconsistent results. The following results are
> exactly the same if the search terms are enclosed in double quotes so
> that they are treated as a phrase.
> I'm searching a Books table for books whose Author column contains
> "cs".
> The following CONTAINS statements in the WHERE clause result in books
> by "Lewis, C.S." being returned:
> CONTAINS( Author, 'cs' )
> CONTAINS( Author, 'CS' )
> CONTAINS( Author, 'C.S.' )
> The fact that "cs" matches an author containing "C.S." indicates that
> both case and punctuation are ignored. However, the following
> CONTAINS statement does not return any results for "Lewis, C.S.":
> CONTAINS( Author, 'c.s.' )
> This puzzles me. It seems as though either punctuation is ignored or
> case is ignored, but not both. What kind of behavior is that? Is
> this by design?
According to BOL, a word is one or more characters without spaces or
punctuation. Punctuation marks such as the period, colon, semicolon,
comma, and hyphen are ignored during a search.
I do not believe the full-text engine is indexing the punctuation marks
which is why the second query fails.
David Gugick
Quest Software
www.quest.com|||I agree about the specification of the word and how it shouldn't contain
punctuation. That is why I also tested it with phrases surrounded by double
quotes, where the specification doesn't mention that puncutation is not
allowed.
I imagine you are right about the indexing, but that doesn't explain why
CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.' ) does not
work, because they both have the exact same punctuation. The only differenc
e
is case.
"David Gugick" wrote:

> Greg Smalter wrote:
> According to BOL, a word is one or more characters without spaces or
> punctuation. Punctuation marks such as the period, colon, semicolon,
> comma, and hyphen are ignored during a search.
> I do not believe the full-text engine is indexing the punctuation marks
> which is why the second query fails.
>
> --
> David Gugick
> Quest Software
> www.quest.com
>|||Greg Smalter wrote:
> I agree about the specification of the word and how it shouldn't
> contain punctuation. That is why I also tested it with phrases
> surrounded by double quotes, where the specification doesn't mention
> that puncutation is not allowed.
> I imagine you are right about the indexing, but that doesn't explain
> why CONTAINS( Author, 'C.S.' ) works while CONTAINS( Author, 'c.s.'
> ) does not work, because they both have the exact same punctuation.
> The only difference is case.
That part is confusing...
David Gugick
Quest Software
www.quest.com