CONTAINS not returning results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CONTAINS not returning results

Author

Topic

furiousp
Starting Member

2 Posts

Posted - 2009-01-23 : 07:31:29

I have a table that's Full-Text indexed. When I search for a string in a column in the table using CONTAINS I don't get any results, but I do get a result if query for the same text using CHARINDEX.

So, this returns 0:


select count(*) from products
where (contains(description, 'XS0377767420'))
and (id = 2071075)

and this returns the correct index of the string:


select charindex('XS0377767420', description) from products
where id = 2071075

I have re-run a full population of the full text indexing, but it's still not working. Is there anything else I can try?

Also, just to note that I can find other text strings in the column using CONTAINS. It seems to only work for some strings.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 08:06:14

try the below

select count(*) from products
where description = 'XS0377767420'
and id = 2071075

select charindex('XS0377767420', description) from products
where id = 2071075

furiousp
Starting Member

2 Posts

Posted - 2009-01-23 : 09:09:32

Forgot to mention the 'description' field is of text datatype. I know I could just use the charindex function, but there are a lot of stored procedures using contains and I don't want to have to change them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources