CONTAINSTABLE v/s FREETEXT

Hi All,
I have one table Customers in Northwind database.
While performing FreeText search I want to give weightage ON Columns for
a searched phrase.
CompanyName weight (1) ContactName weight
(.7)
ContactTitle weight (.5) Address
weight (.5)
If I Searched for "Christina" phrase then results will be displayed base
on weightage as well as rank.
i.e. results having "Christina" in CompanyName should be displayed
first. If I found "Christina" only in Address then this should be last.
Any Ideas?
Regards
AbhijeetAbhijeet,
First of all, all of the tables in Northwind are much too small for proper
consideration of the understanding of CONTAINS* vs. FREETEXT*, in the same
manner as you would not use the Northwind database for performance
benchmarking of production databases. Note, FREETEXT ignores Boolean
comparisons. You need a statistically significant number of rows (100K+) as
well as a significant number of non-noise, unique words for your testing to
give valid results.
Also, since you're trying to "weightage ON Columns", you should review KB
articles: 286787 (Q286787) FIX: Incorrect Results From Full-Text Search on
Several Columns
http://support.microsoft.com/default.aspx?scid=kb;en-us;286787 and 294809
(Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search Across
Columns http://support.microsoft.com/default.aspx?scid=kb;en-us;294809. This
is the default behavior for SQL Server 2000 as well.
Regards,
John
PS: for the fastest (and best) responses, you should post these specific FTS
related questions to the newsgroup: microsoft.public.sqlserver.fulltext
only.
"Abhijeet Raje" <abhijeet2804@.hotmail.com> wrote in message
news:%23HO5DZsQDHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I have one table Customers in Northwind database.
> While performing FreeText search I want to give weightage ON Columns
for
> a searched phrase.
> CompanyName weight (1) ContactName
weight
> (.7)
> ContactTitle weight (.5) Address
> weight (.5)
> If I Searched for "Christina" phrase then results will be displayed
base
> on weightage as well as rank.
> i.e. results having "Christina" in CompanyName should be displayed
> first. If I found "Christina" only in Address then this should be last.
> Any Ideas?
> Regards
> Abhijeet
>

CONTAINSTABLE inconsistency with phrase

Hi

I have a table in Sql Server 2000 with full text indexing setup on a column called 'contents' with a datatype of Text.

The column contains HTML, and I want to search for a particular link eg.

<a href="/hm/default.aspx?i=40559#secure">

..by using the following phrase:

/hm/default.aspx?i=40559

..in a CONTAINSTABLE query.

I need to find the phrase within the html. This is tricky because containstable only allows a wildcard at the end of the phrase, ie I can't search for '*/hm/default.aspx?i=40559*' with an asterisk at each end.

BUT, when I try the following query on my development server, it works without any asterisks:

SELECT item_id, contents, a.RANK FROM
CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a, item b
WHERE (a.[KEY] = b.item_id AND path LIKE '%10646%')

The row is returned but I don't understand why. I thought the full text would try to find the phrase on its own (with no html surrounding it)? On my live server, it doesn't work (also Sql 2000). The data and the FT catalogs are the same on both machines. What else could cause this difference?

Any help greatly appreciated.

Ed

You get the data is because of the join. Try running just the containstable() query by itself and you should notice it.

|||

Thanks.

I tried it without the join as

SELECT item_id, contents FROM
CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a, item b

..but it's not returning the row on my live server. It works ok on my dev server which seems odd?

|||

What do you get for these?

SELECT * FROM CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a

select * from item b where , item b
WHERE LIKE '%10646%'

Also, your original query can be rewriten as this.

SELECT * FROM CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a

join item b on (a.[KEY] = b.item_id AND b.path LIKE '%10646%')

Do you see why you get more rows returned now.

|||

Thanks for your help oj.

I tried

SELECT * FROM CONTAINSTABLE(item, contents, '/hm/default.aspx?i=40559') as a

..but no rows returned.

Works on my dev server no problem, row IS returned.

Still unsure about what's causing this. I tried your rewrite of the query but it's not returning the row either. Am I right in thinking that containstable searches for words (ie with a space at either end) or word prefixes, and if so, why is this working on my dev server in the first place?

Thanks again.

|||

Perhaps, there is no such row exist. Try

SELECT * FROM item

where contents like '/hm/default.aspx?i=40559%'

Also, try update/repopulate your fts.

|||

Thanks oj

The LIKE query returns the row just fine:

SELECT * FROM item
where contents like '%/hm/default.aspx?i=40559%'

I've tried rebuilding and repopulating the catalogs (several times) but it still won't work. One thing I should mention is I deleted the contents of my noise.dat and noise.eng files, but I don't think that's significant.

It seems the only option is to grab the column and do a string.indexof('phrase') method in my code, which is annoying. Still don't get why it works on one envrionment and not the other.

Ed

Contains()

I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
ChristophChristoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph

Contains()

I have the phrase
'One Two Three'
as the value of a column that is indexed for full text searching.
I run the query
SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
and it returns the row with the above value, which is all well
and fine. Now, I need it so that the row would NOT be
returned for the following query:
SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
Basically, I want it so that it looks for the words NEAR each
other but only in the order specified in the query. Such that it
returns rows where the first word is NEAR the second word
but also preceeds it as well.
Looking in the documentation, I didn't see how or if this is
possible. Is it? If so, how?
thnx,
ChristophChristoph,
See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks,
John
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:eGKLwqNyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have the phrase
> 'One Two Three'
> as the value of a column that is indexed for full text searching.
> I run the query
> SELECT * FROM table WHERE CONTAINS(column, 'One NEAR Two' );
> and it returns the row with the above value, which is all well
> and fine. Now, I need it so that the row would NOT be
> returned for the following query:
> SELECT * FROM table WHERE CONTAINS(column, 'Two NEAR One' );
> Basically, I want it so that it looks for the words NEAR each
> other but only in the order specified in the query. Such that it
> returns rows where the first word is NEAR the second word
> but also preceeds it as well.
> Looking in the documentation, I didn't see how or if this is
> possible. Is it? If so, how?
> thnx,
> Christoph
>
>|||> See my reply in the newsgroup: microsoft.public.sqlserver.fulltext.
Thanks. I posted a follow up.
thnx,
Christoph

CONTAINS phrase behavior

Shouldn't matter.
Is the catalog up to date?
Are you searching on the data that is returned? i.e. is the match on data that you are not seeing.
It should just get the phrase.good ideas! Thank you. I just rebuilt and repopulated the index, same negative results. I deleted the index and recreated it. I doubled checked the columns that were indexed compare to the results that are returned. Nothing hidden. I tried "product select" and it returned 0, so it's not really like an AND afterall. Just a flook I guess.

I'm sure there's a user end problem in here somewhere since the query works as expected with other phrases.

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.