Help! "#1" in Contains returns unexpected

Hi all,
Need some help with this. On a simple search I use the syntax;
where title like'%#1%'
returns what I expect. However when I use my advanced search that allows
many criteria, I go to using indexed fulltext search and CONTAINS. I found
that the Like syntax makes the advanced search run way too long, so I am
trying to avoid it. The syntax ends up being:
contains((title ),'("#1*")')
Note I include the * to supposedly behave somewhat similar to the "like"
syantax.
Well now everything with the '1' in it is returned. I know that CONTAINS
ignores some punctuation like single-quotes but why the '#' sign? It does
return anything with '1' in it and that is a very small string.
(Yes I also found out that I can't pass the #1 as a querystring parameter in
ASP.NET but that is for another group.)
Is there anyway I can get CONTAINS to recognize the combination of "#1*"
?
Thanks to all...
John,
Sorry, but # is also punctuation and serves as a wordbreaker. I know of no
wordbreakers that treat it as otherwise, but that is what it would take to
get the # indexed in the full-text indexes. (Perhaps someone knows better.)
I researched this with a full text index on a parts catalog, where (as you
can imagine) there are many, many # characters.
Perhaps you can do something like:
SELECT * FROM
(SELECT ... CONTAINS (contains((title ),'("1*")')...) AS B
WHERE B.Title LIKE '%#1%'
A derived table is not guaranteed to force execution order, so you might put
the fulltext query results into a temp table, then query from that for the
LIKE string.
RLF
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:OM8DROlQIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Need some help with this. On a simple search I use the syntax;
> --
> where title like'%#1%'
> --
> returns what I expect. However when I use my advanced search that allows
> many criteria, I go to using indexed fulltext search and CONTAINS. I found
> that the Like syntax makes the advanced search run way too long, so I am
> trying to avoid it. The syntax ends up being:
> --
> contains((title ),'("#1*")')
> --
> Note I include the * to supposedly behave somewhat similar to the "like"
> syantax.
> Well now everything with the '1' in it is returned. I know that CONTAINS
> ignores some punctuation like single-quotes but why the '#' sign? It does
> return anything with '1' in it and that is a very small string.
> (Yes I also found out that I can't pass the #1 as a querystring parameter
> in ASP.NET but that is for another group.)
> Is there anyway I can get CONTAINS to recognize the combination of "#1*"
> ?
> Thanks to all...
>
|||Thanks Russell,
I will just have to do better research on the peculiarities of fulltext
search and find a way to get the results I need without using up the server
resources. I am expecting at least 100 concurrent users.
Where there is a will...
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23DAO3EmQIHA.2268@.TK2MSFTNGP02.phx.gbl...
> John,
> Sorry, but # is also punctuation and serves as a wordbreaker. I know of
> no wordbreakers that treat it as otherwise, but that is what it would take
> to get the # indexed in the full-text indexes. (Perhaps someone knows
> better.) I researched this with a full text index on a parts catalog,
> where (as you can imagine) there are many, many # characters.
> Perhaps you can do something like:
> SELECT * FROM
> (SELECT ... CONTAINS (contains((title ),'("1*")')...) AS B
> WHERE B.Title LIKE '%#1%'
> A derived table is not guaranteed to force execution order, so you might
> put the fulltext query results into a temp table, then query from that for
> the LIKE string.
> RLF
>
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
> news:OM8DROlQIHA.1208@.TK2MSFTNGP03.phx.gbl...
>
|||Are you able to create a new column for FTS indexing that replaced # with a
"token", eg. HASH. That way you could search for "HASH1" which would be
treated as a word. I do this myself for a number of characters which are
handled as word breakers that I need to be able to search on, and store the
"tokenised" version plus any additional keywords I want indexed in a
separate column to the description itself.
Dan
John wrote on Wed, 19 Dec 2007 15:59:54 -0500:

> Thanks Russell,

> I will just have to do better research on the peculiarities of fulltext
> search and find a way to get the results I need without using up the
> server resources. I am expecting at least 100 concurrent users.

> Where there is a will...
[vbcol=seagreen]
> "Russell Fields" <russellfields@.nomail.com> wrote in message news:%23DAO3EmQIHA.2268@.TK2MSFTNGP02.phx.gbl...
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
|||Daniel,
Interesting concept. I will indeed consider that possibility.
Thanks...
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eHY2iNyQIHA.3916@.TK2MSFTNGP02.phx.gbl...
> Are you able to create a new column for FTS indexing that replaced # with
> a "token", eg. HASH. That way you could search for "HASH1" which would be
> treated as a word. I do this myself for a number of characters which are
> handled as word breakers that I need to be able to search on, and store
> the "tokenised" version plus any additional keywords I want indexed in a
> separate column to the description itself.
> Dan
>
> John wrote on Wed, 19 Dec 2007 15:59:54 -0500:
>
>
>
>
>
>
>
>
>
>
>