The first thing you must do is to create a FULLTEXT index as follows:
ALTER TABLE directory_coop ADD FULLTEXT name_ftndx (name);
That's just the beginning.
The next thing you need to do is understand the SQL syntax for using FULLTEXT indexes.
Please read the MySQL Documentation on FULLTEXT Indexes
I have many posts in the DBA StackExchange about using them.
ts_rank_cd yields a real. If you cast this to double precision, the result becomes:
SET extra_float_digits = 3;
SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'))::double precision;
That explains what you observe, because the real is cast to double precision if you compare it ...
Thanks to @a_horse_with_no_name, this is how I could solve the issue:
SELECT id, reference_date, creation_datetime, completion_datetime, status,
channel, index, archived, country_id, creator, notes, has_alerts
SELECT id, reference_date, creation_datetime, completion_datetime,
status, archived, country_id, channel, index, creator, ...
The difference is caused by noise word "W". By default SQL Server uses stoplist to exclude some short or frequently used words like "and", "does", "could". You can see the full list using such query:
select * from sys.fulltext_system_stopwords where language_id = 1033
And you can disable the stoplist for your full ...
Also on SQL-Server 2014 has the msfte.dll the mentioned bug.
I solved it by register the OS RTF IFilter used for Windows Search into SQL-Server using this registry key:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSearch\Filters\.rtf]
You would have to make your own parser. This is not easy to do. You would also have to decide what features of the parser to keep and what to throw away. It isn't clear to me that the file token type is the only one that will give you problems. There is a test module 'test_parser' which could serve as an example of creating your own, but it much too ...
For starters: A German poet is still a Poet. 'Poet' <> 'Pöt'; 'oe' <> 'ö'. The convention to replace 'ö' with 'oe' is largely out of use nowadays. See:
I have had related problems many times. The additional module unaccent is instrumental to cover spelling variants and typos on both ...
That's because full-text search treats hyphenated words specially:
SELECT to_tsvector('english', 'iDream Ice cream iScream');
'cream':3 'ice':2 'idream':1 'iscream':4
The numbers behind the lexemes mark the position they had in the original text (cream is the ...