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

Help with WHERE CLAUSE

I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.

Here is what i have tried:

I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag.
The table looks like this;

tblClientAddresses:
Address_ID,Client_ID,Address,Address2,City,State,Zip,Country,AddressType,StartDate,
EndDate,MailTo

WHERE (A.MailTo=1) AND (A.EndDate Is Null OR DatePart(mm,A.Enddate) >= DatePart(mm,GETDATE()) AND DatePart(dd,A.Enddate) >= DatePart(dd,GETDATE()))

Thank you for any help!I have tried this:

WHERE (A.MailTo=1) AND (Month(Day(GETDATE())) BETWEEN Month(Day(A.Startdate)) AND(Month(Day(A.Enddate))) OR (A.Startdate Is Null) AND (A.EndDate Is Null))

But it still not returning the proper row.

Does anyone have an idea if what i show above is even doable?

Thanks|||Try this:

WHERE (A.MailTo=1)
AND Convert(varchar(5),GetDate(),10) BETWEEN
Convert(varchar(5),A.Startdate,10) AND Convert(varchar(5),A.Enddate,10)

The datetime format #10 is mm-dd-yy. If you limit the cast to varchar(5) you only get mm-dd.

JK|||That has fixed the date issue, but now the MailTo flag is being ignored. Should I adjust the paranthises somehow? I have moved the flag to the end and added paranthises but no luck.

Thank you very much|||Hmm. It is odd that the MailTo flag is being ignored.

Could you post your full query command with all the trimmings?
JK