containstable, top_n_rank, and additional where clause combination causes unexpected resul

Hello,
Something strange happens if I try and do the following:
If I use the containstable function, with top_n_rank, along with an
extra "and where" clause, then the top_n_rank does not seem to return
the correct number of rows.
Here is an example to explain my point:
select *, tempidentity = IDENTITY (INT) into #tempt
from Store_BasicSearchableShelves,
containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
@.nextitemsrecpointer) tblSearchResults
where [key] = Store_BasicSearchableShelves.ShelfId
and ParentAisleId = @.PID
order by rank desc
If @.nextitemsrecpointer is equal to 20, then the stored proc only
returns 14 rows. If I remove the additional "and ParentAisleId =
@.PID" where clause, then the stored proc returns 20 rows, which is
correct. If I begin to edit the value of @.nextitemsrecpointer for
experimentational purposes to a higher value such as 30 or 40, then
the stored proc returns more rows - 18 and 25 respectively.
The additional "and ParentAisleId = @.PID" seems to be affecting the
way that top_n_rank is behaving.
Can anyone please provide me with a work around for this?
Thank you,
Regards, dnw.
Your problem is that when you limit your result set that is returned from
MSSearch further rows are removed by the "and ParentAisleId = @.PID" clause.
The approaches to this problem are 1) knowing in advance the number of rows
which are returned by MSSearch for this query and entering this value for
@.nextitemrecpointer, 2) partitioning your table into multiple partitioned
tables one for each PartentAisleID value so your query would end up looking
something like this:
if @.ParentAisleID=1
begin
select *, tempidentity = IDENTITY (INT) into #tempt
from Store_BasicSearchableShelves_1,
containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
@.nextitemsrecpointer) tblSearchResults
where [key] = Store_BasicSearchableShelves.ShelfId
order by rank desc
end
if @.ParentAisleID=2
begin
select *, tempidentity = IDENTITY (INT) into #tempt
from Store_BasicSearchableShelves_2,
containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
@.nextitemsrecpointer) tblSearchResults
where [key] = Store_BasicSearchableShelves.ShelfId
order by rank desc
end
3) picking a larger number which will guarantee a larger number of hits that
when filtered by @.ParentAisleID will yield at least @.nextitemsrecpointer
hits. You have to be careful here as you don't want to pick too large a
number to guarantee hits.
In large search applications partitioning in frequently used and it does
work very well. They will often have a seperate catalog for each of the
partitioned tables and you will then get a seperate threads for each catalog
which will improve your overall querying and indexing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dot net work" <dotnw@.hotmail.com> wrote in message
news:77b8c5a9.0410280048.698a85c7@.posting.google.c om...
> Hello,
> Something strange happens if I try and do the following:
> If I use the containstable function, with top_n_rank, along with an
> extra "and where" clause, then the top_n_rank does not seem to return
> the correct number of rows.
> Here is an example to explain my point:
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> and ParentAisleId = @.PID
> order by rank desc
>
> If @.nextitemsrecpointer is equal to 20, then the stored proc only
> returns 14 rows. If I remove the additional "and ParentAisleId =
> @.PID" where clause, then the stored proc returns 20 rows, which is
> correct. If I begin to edit the value of @.nextitemsrecpointer for
> experimentational purposes to a higher value such as 30 or 40, then
> the stored proc returns more rows - 18 and 25 respectively.
> The additional "and ParentAisleId = @.PID" seems to be affecting the
> way that top_n_rank is behaving.
> Can anyone please provide me with a work around for this?
> Thank you,
> Regards, dnw.
|||DNW,
Could you post the SQL Server and OS platform information from -- SELECT
@.@.version -- as well as a row count from your table
Store_BasicSearchableShelves? As all of this information is important in
first understanding your environment before making recommendations as well
as understanding the existing RANK values that are returned from your query.
The simple answer to your as why you query is not returning the expected
number of rows when using Top_N_Rank and with an additional WHERE clause is
that all of the WHERE clause parameters are applied AFTER the MSSearch
service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
Additionally, and depending upon the number of rows in your table, the
actual number or "top" values for RANK may be not what you expect as in
order to calculate rank, a statically large number of rows need to be
present.
You should also review the following KB article on the use and cautions of
using Top_N_Rank: 240833 (Q240833) "FIX: Full-Text Search Performance
Improved via Support for TOP" at
http://support.microsoft.com//defaul...b;EN-US;240833
Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#4HNUAOvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Your problem is that when you limit your result set that is returned from
> MSSearch further rows are removed by the "and ParentAisleId = @.PID"
clause.
> The approaches to this problem are 1) knowing in advance the number of
rows
> which are returned by MSSearch for this query and entering this value for
> @.nextitemrecpointer, 2) partitioning your table into multiple partitioned
> tables one for each PartentAisleID value so your query would end up
looking
> something like this:
> if @.ParentAisleID=1
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_1,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> if @.ParentAisleID=2
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_2,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> 3) picking a larger number which will guarantee a larger number of hits
that
> when filtered by @.ParentAisleID will yield at least @.nextitemsrecpointer
> hits. You have to be careful here as you don't want to pick too large a
> number to guarantee hits.
> In large search applications partitioning in frequently used and it does
> work very well. They will often have a seperate catalog for each of the
> partitioned tables and you will then get a seperate threads for each
catalog
> which will improve your overall querying and indexing.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dot net work" <dotnw@.hotmail.com> wrote in message
> news:77b8c5a9.0410280048.698a85c7@.posting.google.c om...
>
|||Thanks a lot for your advice.
-dnw.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:<#4HNUAOvEHA.2540@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Your problem is that when you limit your result set that is returned from
> MSSearch further rows are removed by the "and ParentAisleId = @.PID" clause.
> The approaches to this problem are 1) knowing in advance the number of rows
> which are returned by MSSearch for this query and entering this value for
> @.nextitemrecpointer, 2) partitioning your table into multiple partitioned
> tables one for each PartentAisleID value so your query would end up looking
> something like this:
> if @.ParentAisleID=1
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_1,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> if @.ParentAisleID=2
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_2,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> 3) picking a larger number which will guarantee a larger number of hits that
> when filtered by @.ParentAisleID will yield at least @.nextitemsrecpointer
> hits. You have to be careful here as you don't want to pick too large a
> number to guarantee hits.
> In large search applications partitioning in frequently used and it does
> work very well. They will often have a seperate catalog for each of the
> partitioned tables and you will then get a seperate threads for each catalog
> which will improve your overall querying and indexing.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dot net work" <dotnw@.hotmail.com> wrote in message
> news:77b8c5a9.0410280048.698a85c7@.posting.google.c om...
|||Hi John,
[vbcol=seagreen]
SELECT
@.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
[vbcol=seagreen]
At the moment, only 39.
[vbcol=seagreen]
expected
number of rows when using Top_N_Rank and with an additional WHERE
clause is
that all of the WHERE clause parameters are applied AFTER the MSSearch
service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
As I am a newbie, please can you explain the difference between those
3 things please - Top_N_Rank, Top_N_Row and top_n_by_RANK. Thanks.
Thank you,
Regards, dnw.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#iLXshRvEHA.3840@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> DNW,
> Could you post the SQL Server and OS platform information from -- SELECT
> @.@.version -- as well as a row count from your table
> Store_BasicSearchableShelves? As all of this information is important in
> first understanding your environment before making recommendations as well
> as understanding the existing RANK values that are returned from your query.
> The simple answer to your as why you query is not returning the expected
> number of rows when using Top_N_Rank and with an additional WHERE clause is
> that all of the WHERE clause parameters are applied AFTER the MSSearch
> service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
> Additionally, and depending upon the number of rows in your table, the
> actual number or "top" values for RANK may be not what you expect as in
> order to calculate rank, a statically large number of rows need to be
> present.
> You should also review the following KB article on the use and cautions of
> using Top_N_Rank: 240833 (Q240833) "FIX: Full-Text Search Performance
> Improved via Support for TOP" at
> http://support.microsoft.com//defaul...b;EN-US;240833
> Regards,
> John
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#4HNUAOvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> clause.
> rows
> looking
> that
> catalog
|||Thanks, DNW,
While the version (SQL & OS platform) are less important for your questions,
overall the OS platform is most important for understanding expected FTS
query results when searching on specific words &/or punctuation characters
due to OS-specific wordbreaker issues, see
http://groups.google.com/groups?q=langwrbk+infosoft for details.
However, in this case the row count is the most important factor, especially
when used with Top_N_Rank. I'd recommend that you review SQL Server 2000 BOL
title "Full-Text Search Recommendations" and the next to last paragraph on
RANK for a better understanding of how RANK is calculated in SQL Sever 2000.
Rank needs a "statistically significant" number of rows (and therefore
number of unique non-noise words) in order to be useful and 39 rows is not a
"statistically significant" number of rows. While it may depend upon the
number of unique non-noise words, the number of rows is important as well,
and at least 10,000+ rows are generally the recommended number of rows to
start using RANK and you won't need Top_N_Rank for performance reasons
until at least 1 million rows.
As for "Top_N_Rank, Top_N_Row and top_n_by_RANK", there is only Top_N_Rank,
the other two were only metaphors that I used in my explanation as while
Top_N_Rank does limit the number of rows returned it is in fact a limit for
N (some number) of rows returned by RANK and not explicitly a row limiter as
is Top. Sorry, for the confusion, but with only 39 rows, I'd recommend that
you do not use Top_N_Rank as it was added as a fix in SQL Server 7.0 (and
included in SQL Server 2000) to improve the FTS query performance when used
against very large (1 to 2+ million) row tables that can generate large FT
Catalogs. See KB article 240833 (Q240833) for more info.
Again, thanks for providing the @.@.version as well as the row count info!
John
"Dot net work" <dotnw@.hotmail.com> wrote in message
news:77b8c5a9.0410290622.8147ced@.posting.google.co m...
> Hi John,
> SELECT
> @.@.version
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> At the moment, only 39.
> expected
> number of rows when using Top_N_Rank and with an additional WHERE
> clause is
> that all of the WHERE clause parameters are applied AFTER the MSSearch
> service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
> As I am a newbie, please can you explain the difference between those
> 3 things please - Top_N_Rank, Top_N_Row and top_n_by_RANK. Thanks.
> Thank you,
> Regards, dnw.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<#iLXshRvEHA.3840@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
well[vbcol=seagreen]
query.[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
from[vbcol=seagreen]
for[vbcol=seagreen]
partitioned[vbcol=seagreen]
hits[vbcol=seagreen]
@.nextitemsrecpointer[vbcol=seagreen]
a[vbcol=seagreen]
does[vbcol=seagreen]
the[vbcol=seagreen]
return[vbcol=seagreen]
|||That info was really interesting! Thanks a lot.
-dnw.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<emMzmVdvEHA.2200@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Thanks, DNW,
> While the version (SQL & OS platform) are less important for your questions,
> overall the OS platform is most important for understanding expected FTS
> query results when searching on specific words &/or punctuation characters
> due to OS-specific wordbreaker issues, see
> http://groups.google.com/groups?q=langwrbk+infosoft for details.
> However, in this case the row count is the most important factor, especially
> when used with Top_N_Rank. I'd recommend that you review SQL Server 2000 BOL
> title "Full-Text Search Recommendations" and the next to last paragraph on
> RANK for a better understanding of how RANK is calculated in SQL Sever 2000.
> Rank needs a "statistically significant" number of rows (and therefore
> number of unique non-noise words) in order to be useful and 39 rows is not a
> "statistically significant" number of rows. While it may depend upon the
> number of unique non-noise words, the number of rows is important as well,
> and at least 10,000+ rows are generally the recommended number of rows to
> start using RANK and you won't need Top_N_Rank for performance reasons
> until at least 1 million rows.
> As for "Top_N_Rank, Top_N_Row and top_n_by_RANK", there is only Top_N_Rank,
> the other two were only metaphors that I used in my explanation as while
> Top_N_Rank does limit the number of rows returned it is in fact a limit for
> N (some number) of rows returned by RANK and not explicitly a row limiter as
> is Top. Sorry, for the confusion, but with only 39 rows, I'd recommend that
> you do not use Top_N_Rank as it was added as a fix in SQL Server 7.0 (and
> included in SQL Server 2000) to improve the FTS query performance when used
> against very large (1 to 2+ million) row tables that can generate large FT
> Catalogs. See KB article 240833 (Q240833) for more info.
>
> Again, thanks for providing the @.@.version as well as the row count info!
> John
>
> "Dot net work" <dotnw@.hotmail.com> wrote in message
> news:77b8c5a9.0410290622.8147ced@.posting.google.co m...
> news:<#iLXshRvEHA.3840@.TK2MSFTNGP12.phx.gbl>...
> well
> query.
> is
> of
> from
> clause.
> rows
> for
> partitioned
> looking
> hits
> that
> @.nextitemsrecpointer
> a
> does
> the
> catalog
> return

containstable ignored words

Dear sir,

when i am using containtable i am getting sql error saying

Microsoft OLE DB Provider for SQL Server error '80040e14'

A clause of the query contained only ignored words.

what are ignored words and how to avoid this error.

Any suggestion??Ignored words(noise words) are words such as:a, as, etc
you could check them( add/remove) in the default installation path:
C:\Program Files\Common Files\System\MSSearch\Data\Config

You also may encounter Error 7619, "The query contained only ignored words" when using any of the full-text predicates in a full-text query, such as CONTAINS(pr_info, 'between AND king'). The word "between" is an ignored or noise word and the full-text query parser considers this an error, even with an OR clause. Consider rewriting this query to a phrase-based query, removing the noise word, or options offered in Knowledge Base article Q246800, "INF: Correctly Parsing Quotation Marks in FTS Queries". Also, consider using Windows 2000 Server: there have been some enhancements to the word-breaker files for Indexing Services.

'Contains' problem

Hello,
I have a problem with a contains clause. I believe I have the full-text
index set correctly. Whenever I put a '-' (dash) character in the query, it
seems to return incorrect results.
Here is a query that works.
SELECT DISTINCT Store_Products.Family As [key] FROM Store_Products WHERE
CONTAINS(Store_Products.*, ' "suit*" ')
Here is one that does not.
SELECT DISTINCT Store_Products.Family As [key] FROM Store_Products WHERE
CONTAINS(Store_Products.*, ' "9-076*" ')
Now, if I change the query to the following, taking out the 'dash'
character, it returns what it should.
SELECT DISTINCT Store_Products.Family As [key] FROM Store_ProductsWHERE
CONTAINS(Store_Products.*, ' "076*" ')
I am not sure where to look to fix this. Thank you for your time.this may be helpful for you:
PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
Queries
http://support.microsoft.com/kb/200043/EN-US/|||very helpful. thank you.
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1110237167.585809.173180@.f14g2000cwb.googlegroups.com...
> this may be helpful for you:
> PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
> Queries
> http://support.microsoft.com/kb/200043/EN-US/
>

CONTAINS on sql2005

I've just discovered that the CONTAINS clause on sql2005 can search
multiple columns - great!
Is there a way to return which column the search word(s) were found in?
Dunc
Regretably not.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dunc" <duncan.welch@.gmail.com> wrote in message
news:1140444800.738404.90590@.g47g2000cwa.googlegro ups.com...
> I've just discovered that the CONTAINS clause on sql2005 can search
> multiple columns - great!
> Is there a way to return which column the search word(s) were found in?
> Dunc
>
|||On what edition did it work - just enterprise edition or even on standard
(and if so what is required to get it going?)
Jan
"Dunc" <duncan.welch@.gmail.com> schrieb im Newsbeitrag
news:1140444800.738404.90590@.g47g2000cwa.googlegro ups.com...
> I've just discovered that the CONTAINS clause on sql2005 can search
> multiple columns - great!
> Is there a way to return which column the search word(s) were found in?
> Dunc
>

Contains clause with only NOT keywords

Hello everyone,
I posted this on sqlserver.programming and it was recommended I try this
group.
I am designing a search screen that searches for keywords in Text fields as
well as searching other related tables with fields like Date ranges and
other lookup code fields. One of our users asked why they can't use a
Date-Range search in conjunction with keywords NOT found in the free text. I
have read that it is not possible to do with Contains.
For example, a standard keyword search might create this Contains clause:
contains((desciption),'("cat" & "dog") and ("horse") &! "cow" &! "bull"')
The users just want to use the &! "cow" &! "bull" part of the Contains query
along with other more standard Where criteria, for example "and OrderDate >
'10/10 2006' ".
I have tried to pass "noise" words for the first part of the Contains, but
they are ignored.
I also tried separating out the NOT keywords into a series of " and not
description like 'bull%' " type filters, but the performance becomes
intolerably slow.
Is there any way to get around this problem? Maybe some crafty trickery?
Thanks to all...
You have to parse your query so that it looks like this:
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull")')
I have upper cased the boolean operators for clarity.
For your date query it would look like this
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull")')
where orderdate>'2007-01-01'
You cannot search on a date string and hope for it to be interpreted as a
date and do inequality operations on it. So I could not do something like
this
select * from John where contains(*,'("cat" AND "dog" AND "horse") AND NOT
( "cow" AND "bull") and OrderDate>'2007-01-01')
as sql FTS can only interpret the date string as a string and only do not
equal or equal operations against it.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Kotuby" <JohnKotuby@.discussions.microsoft.com> wrote in message
news:%23MZFHLUIIHA.5352@.TK2MSFTNGP03.phx.gbl...
> Hello everyone,
> I posted this on sqlserver.programming and it was recommended I try this
> group.
> I am designing a search screen that searches for keywords in Text fields
> as
> well as searching other related tables with fields like Date ranges and
> other lookup code fields. One of our users asked why they can't use a
> Date-Range search in conjunction with keywords NOT found in the free text.
> I
> have read that it is not possible to do with Contains.
> For example, a standard keyword search might create this Contains clause:
> contains((desciption),'("cat" & "dog") and ("horse") &! "cow" &! "bull"')
> The users just want to use the &! "cow" &! "bull" part of the Contains
> query
> along with other more standard Where criteria, for example "and OrderDate
> '10/10 2006' ".
> I have tried to pass "noise" words for the first part of the Contains, but
> they are ignored.
> I also tried separating out the NOT keywords into a series of " and not
> description like 'bull%' " type filters, but the performance becomes
> intolerably slow.
> Is there any way to get around this problem? Maybe some crafty trickery?
> Thanks to all...
>
>
|||FYI - the original thread can be found here:
[url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
As I understand it, he OP was trying to find out how to combine a negative
FTI search (using CONTAINS) with additional restrictions in the WHERE caluse.
I suggested building the condition using NOT(CONTAINS()).
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||thanks ML - that is an interesting approach. That should work, but it would
be expensive if the results set was large.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ML" <ML@.discussions.microsoft.com> wrote in message
news:329CBEFC-00B7-4396-B16C-79C5B6461DCB@.microsoft.com...
> FYI - the original thread can be found here:
> [url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
> As I understand it, he OP was trying to find out how to combine a negative
> FTI search (using CONTAINS) with additional restrictions in the WHERE
> caluse.
> I suggested building the condition using NOT(CONTAINS()).
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/

CONTAINS clause with multi-word "AND" inflectional searching?? help...

What if you want to search using FTS with AND logic using the FORMSOF(inflectional,...) inside the CONTAINS() clause?

if my search phrase is "light hearted" I can easily do an OR search using the following in my where clause:
CONTAINS(Colname,'formsof(INFLECTIONAL,light,heart ed).
but the and is far more tricky...

does anyone know how to do this without having multiple Contains statements (which greatly increases overhead)?

I know that I can use AND in a straight contains like so:
CONTANS(column, '"light" AND "hearted"') but this does not allow me to explore inflectional variations on the words...

nesting multiple FORMSOF's doesn't seem to work either like so:
contains(column,'"formsof(inflectional,light)"' AND 'formsof(inflectional,hearted)"')

anyone else found how to do this?just to clarify I think 2 better search words for my ex. would have been "sport" and "award" and it's really important that I get results for Sports, Sported, Awards, and Awarded. Then I'd have any combination of the two words Inflectional variations (one from each root word) that exist in the same record returned in my resultset.

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

Contains Clause and Single Characters

I have a commerce server database that uses a full text index to search
product numbers and descriptions. My issue is that some of my product
numbers contain single characters, "EMT 1" for example, and the Contains
clause does not seem to treat this phrase as an exact match search when I do
something like
select *
from <table>
where contains(*, '"EMT 1"')
It finds that product and a number of other products that happen to contain
EMT in the description. Is there any way that I can get the index to
recognize the entire string? It seems that it treats the "1" as a noise word
and just drops it from the criteria.
I have tried stripping out all puncuation and white space from the product
number and adding that value to the FT Index but it complicates any Keword
searches that may have valid white space in that I can not universally strip
white space out of the criteria that the user entered to try to get a match.
Chris
cbuda wrote on Wed, 20 Jul 2005 06:54:06 -0700:

> I have a commerce server database that uses a full text index to search
> product numbers and descriptions. My issue is that some of my product
> numbers contain single characters, "EMT 1" for example, and the Contains
> clause does not seem to treat this phrase as an exact match search when I
> do something like
> select *
> from <table>
> where contains(*, '"EMT 1"')
> It finds that product and a number of other products that happen to
> contain EMT in the description. Is there any way that I can get the index
> to recognize the entire string? It seems that it treats the "1" as a
> noise word and just drops it from the criteria.
> I have tried stripping out all puncuation and white space from the product
> number and adding that value to the FT Index but it complicates any Keword
> searches that may have valid white space in that I can not universally
> strip white space out of the criteria that the user entered to try to get
> a match.
Edit the noise word file for the language you are using, removing everything
but leaving a single line with a space on it. Then rebuild the index -
everything will now be indexed.
Dan
|||Works perfectly, thanks!
"Daniel Crichton" wrote:

> cbuda wrote on Wed, 20 Jul 2005 06:54:06 -0700:
>
> Edit the noise word file for the language you are using, removing everything
> but leaving a single line with a space on it. Then rebuild the index -
> everything will now be indexed.
> Dan
>
>

CONTAINS and WHERE Clause Combination taking too long

Hi,

I have a table with 3 columns and 20 million records.
first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000).
I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column.
Now when i try to search

SELECT

TOP 20

col1,
col3

FROM

tbl

WHERE

col1 = '1234'

AND

CONTAINS(col3,'"market*"')

I am facing following problems
1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second.
2- Some time it show criteria is too complex, although i am only requesting a single word in col3.

I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.

Please assist.

As of now, Fulltext index runs separately from the SQL Engine which means that you cannot influence the to be parsed subset of data on the fulltext catalog. Although the data from the relational query will only bring back 1 row, the whole fulltext will be parsed to findt he appropiate matches although they will be discarded later upon joining the two resultsets.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks, i was already affraid that it will be the case.
In this case what you suggest? Because LIKE '%%' is killing my performance.
Any suggestion will be appreciated.

|||

Make sure you optimized the speed of Fulltext (like separate spindles etc).

Jens K. Suessmeyer

http://www.sqlserver2005.de