CONTAINS with *

Hi,
I read in MSDN that
If the text and asterisk are not delimited by double quotation marks, as in
CONTAINS (column, 'text*'), full-text search considers the asterisk as a
character and will search for exact matches to text*.
However... I have this code:
If chkOptions.Items(1).Selected = True Then 'Starts With...
strText = "" & strText & "*" & ""
End If
If chkOptions.Items(0).Selected = True Then 'Allow Flexionary Forms...
strText = "FormsOf(INFLECTIONAL," & strText & ")"
End If
strSearch = "SELECT " & _
"ST.[Rank], Titles.title as Title, Titles.notes as Notes, Authors.au_fname
as [F.Name], Authors.au_lname as [L.Name] " & _
"FROM " & _
"ContainsTable (Titles, *, '" & strText & "') as ST " & _
"INNER JOIN Titles ON ST.[Key] = Titles.title_id " & _
"INNER JOIN TitleAuthor ON Titles.title_id = TitleAuthor.title_id " & _
"INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id " & _
"ORDER BY ST.[Rank] DESC"
I enter the string cook as strText. Then I build strText as "cook*"
(containing the double quotes).
For some reason, which I have yet to understand, VB takes away my double
quotes, so in the end strSearch comes out as:
SELECT ST.[Rank], Titles.title as Title, Titles.notes as Notes,
Authors.au_fname as [F.Name], Authors.au_lname as [L.Name] FROM
ContainsTable(Titles, *, 'cook*') as ST INNER JOIN Titles ON ST.[Key] =
Titles.title_id INNER JOIN TitleAuthor ON Titles.title_id =
TitleAuthor.title_id INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id
ORDER BY ST.[Rank] DESC
Anyway, my question is this - since as per MSDN the engine searches for the
string 'cook*' (containing the asterisc), it should NOT find any (I have no
string 'cook*' in my table). However, it returns the exact same result as if
I only searched for 'cook', without any asterisc - I get back two records.
The "INFLECTIONAL" part, though,works just fine.
I think I should go to bed now - apparently I can't see something obvious
;-)))
Thank you.
Alex.
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:%234z9qZEBFHA.1084@.tk2msftngp13.phx.gbl...
> strText = "" & strText & "*" & ""

> I enter the string cook as strText. Then I build strText as "cook*"
> (containing the double quotes).
> For some reason, which I have yet to understand, VB takes away my double
> quotes, so in the end strSearch comes out as:
Change the above line to
strText = """" & strText & "*" & """"
A "" by itself is an empty string (you have the double quotes to enclose a
string, and there's nothing between them). A """" tells VB you want a
literal " in a string (you need 2 of them as VB would treat a single one as
the end of a string enclosure and then error because it thinks you have
another opening string quote after it).

> Anyway, my question is this - since as per MSDN the engine searches for
> the string 'cook*' (containing the asterisc), it should NOT find any (I
> have no string 'cook*' in my table). However, it returns the exact same
> result as if I only searched for 'cook', without any asterisc - I get back
> two records.
Because you have not enclosed the cook* in double quotes (see above for how
to fix this) the * is being treated as a wildcard, hence cook is a match.
With the change to the one line above, it should work as you expect.
Dan

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...
>