May 31, 2011 at 2:44 pm
Hi,
I have the following full-text-search query:
SELECT *
FROM table1
WHERE CONTAINS(col1, N'"AB-C-123456*"')
I'm looking for any occurences of string "AB-C-123456" followed by any sequence of characters.
The problem is this does not work; returns no records. I know for a fact that I have occurences of "AB-C-123456" in the column.
On the other hand, this works (returns 2 records):
SELECT *
FROM table1
WHERE CONTAINS(col1, N'"AB-C-123456"')
This works too (same 2 records):
SELECT *
FROM table1
WHERE CONTAINS(col1, N'"123456*"')
Are the dashes in the search string messing things up?
Here is a url I found useful:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 22, 2011 at 6:46 am
I have the exact same problem, if you found something to resolve it please let me know !
Thanks !
September 22, 2011 at 9:29 am
j_bolduc (9/22/2011)
I have the exact same problem, if you found something to resolve it please let me know !Thanks !
Sorry, I don't have much new to report on this issue.
I do know 2 things:
(1) Suffix-based wildcard searches are not supported in FTS;
eg. the following would not be valid:
SELECT *
FROM table1
WHERE CONTAINS(col1, N'"*123456"')
(2) FTS treats multi-word strings as collections of individual searchable items.
For example, the following query would return strings starting with "too" followed by words starting with "many", followed by words starting with "words", ie. three wild-card prefixes, instead of one:
SELECT *
FROM table1
WHERE CONTAINS(col1, N'"too* many words"')
Info taken from "Pro Full-Text Search in SQL Server 2008", by Michael Coles and Hilary Cotter, Apress.
Perhaps the "-" issue we are dealing with is somehow related to point (2) above...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 22, 2011 at 11:06 am
Perhaps the "-" issue we are dealing with is somehow related to point (2) above...
Thanks for the reply
I have the same problem even with no "-" in the search term.
I use CONTAINS(title, '"eclats autres libertes*"') and it returns nothing, it should return something because title LIKE 'eclats autres libertes%' returns something.
Sounds like a bug in the contains function... no solution yet 🙁
November 2, 2011 at 12:05 pm
I was using FTS on Sql 2008 Express R2 and had some issues that sound very similar to what you are experiencing. After installing SP1, queries worked as expected. Do you have any SP's installed? (BTW, I briefly looked, but couldn't find any documentation from MS for SP1 regarding fixes for FTS...)
Also, does your db have a STOPLIST?
November 2, 2011 at 1:14 pm
Dave Mason (11/2/2011)
I was using FTS on Sql 2008 Express R2 and had some issues that sound very similar to what you are experiencing. After installing SP1, queries worked as expected. Do you have any SP's installed? (BTW, I briefly looked, but couldn't find any documentation from MS for SP1 regarding fixes for FTS...)Also, does your db have a STOPLIST?
Not sure if my db has a stoplist, how can I check?
Also, I am on SQL 2008 SP2.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply