Help! "#1" in Contains returns unexpected

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Kotuby - 19 Dec 2007 15:29 GMT

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

Reply to this Message

Russell Fields - 19 Dec 2007 17:04 GMT

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

> Hi all,
> Need some help with this. On a simple search I use the syntax;
[quoted text clipped - 23 lines]
>
> Thanks to all....

Reply to this Message

John Kotuby - 19 Dec 2007 20:59 GMT

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

> John,
>
[quoted text clipped - 42 lines]
>>
>> Thanks to all....

Reply to this Message

Daniel Crichton - 20 Dec 2007 16:14 GMT

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

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

>>> Hi all,
>>> Need some help with this. On a simple search I use the syntax;
[quoted text clipped - 9 lines]
>>> 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....

Reply to this Message

John Kotuby - 27 Dec 2007 17:10 GMT

Daniel,
Interesting concept. I will indeed consider that possibility.

Thanks...

> 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
[quoted text clipped - 66 lines]
>
> >>> Thanks to all....

Reply to this Message