CONTAINS clause problem with single-quotes

Hi all,
This is cross posted to sqlserver.prgramming also...
I am confused by the MSDN help regarding the Contains clause for fulltext
search.
The Help states that the single-quote does not need to be escaped for
CONTAINS but must be escaped for FREETEXT.
Here is an example of a CONTAINS put together by my search program which
does not produce an error:
AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
order by list_Name
The fonts in my email are not correctly representing the clause. I have
replaced ' in the word girl's with 2 single quotes as in typical escaping
for strings...similar to a simple:
(Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
course produces a syntax error.
The problem is that I expect only list_names that contain "girl's" to be
returned. However, with the CONTAINS clause using escape for single-quote
also returns any list_name that contains 'girl' as well.
When I don't double up the quotes, the CONTAINS clause returns an error "
syntax error near 's' ", not what is expected by the online Help.
Anybody have some help for me?
John,
SQL Server fulltext indexes do not support searching for punctuation. In
the string 'girl''s' the quote character is a word-breaker, so you wind up
with two words 'girl' and 's'. In most cases 's' is a noise word and gets
dropped altogether.
If you are looking for punctuation, you will have to combine a full text
query clause with a string search clause such as:
AND summary LIKE '%girl''s%'
RLF
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> This is cross posted to sqlserver.prgramming also...
> I am confused by the MSDN help regarding the Contains clause for fulltext
> search.
> The Help states that the single-quote does not need to be escaped for
> CONTAINS but must be escaped for FREETEXT.
> Here is an example of a CONTAINS put together by my search program which
> does not produce an error:
> AND ( contains((summary),'("girl''s") and ("children''s" | "dog''s")')
> OR contains((List_Name),'("girl''s") and ("children''s" | "dog''s")') )
> order by list_Name
> The fonts in my email are not correctly representing the clause. I have
> replaced ' in the word girl's with 2 single quotes as in typical escaping
> for strings...similar to a simple:
> (Where list_name = 'girl''s'), because (Where list_name = 'girl's') of
> course produces a syntax error.
> The problem is that I expect only list_names that contain "girl's" to be
> returned. However, with the CONTAINS clause using escape for single-quote
> also returns any list_name that contains 'girl' as well.
> When I don't double up the quotes, the CONTAINS clause returns an error "
> syntax error near 's' ", not what is expected by the online Help.
> Anybody have some help for me?
>
|||Thanks Russell,
That makes perfectly good sense. I appreciate the help.
Happy holidays.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OAQrdmLQIHA.4912@.TK2MSFTNGP06.phx.gbl...
> John,
> SQL Server fulltext indexes do not support searching for punctuation. In
> the string 'girl''s' the quote character is a word-breaker, so you wind up
> with two words 'girl' and 's'. In most cases 's' is a noise word and gets
> dropped altogether.
> If you are looking for punctuation, you will have to combine a full text
> query clause with a string search clause such as:
> AND summary LIKE '%girl''s%'
> RLF
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
> news:eOOmzQCQIHA.5980@.TK2MSFTNGP04.phx.gbl...
>