New answers tagged full-text-search
It seems the problem is caused by 8 (and 0,1,2,3,4,5,6,7 and 9) being on the stopword list. A workaround is to base the table on an empty stopword list (or at least one without the 1-digit numbers): CREATE FULLTEXT STOPLIST empty_stoplist; ALTER FULLTEXT INDEX ON test SET STOPLIST empty_stoplist; ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;
Seems that this is the bug: SQL Server 2008 full-text query may return false positives using number with more than 9 digits as search predicate condition. Unfortunately the resolution does not work for Danish language.
Found the solution myself: SELECT * FROM table WHERE column REGEXP 'myValue(\r\n.*)?$'
I do not believe that is a supported feature of SQL Server full text searching. That would require a wildcard resolution to words and then a thesaurus lookup of each matching word to gather the thesaurus terms. This basically maps to a pretty complex query: some one of a group of prefixed words is very near to some one of another group of prefixed words ...
ts_rank will rank all rows, most of which will probably have a ranking of 0. So it's faster to do a search without ts_rank and then ts_rank the results of that search using the same query. So you'd probably want to do something like SELECT score.id, ts_rank(score.contents::tsvector, plainto_tsquery('string')) as rank from (SELECT id, contents FROM ...
Top 50 recent answers are included