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