asked
SQL Server Full text search Contains function
İ have a problem with contains function, when i search with like '%ZAM%' operator, it finds all word that contains ZAM like ZAMANLAMA AZAMI ZAM and etc.. but when I use fts index contains function, it just find ZAM ZAMANLAMA but it doesnt find AZAMI or 123ZAM789. I have also tried CONTAINS (YourColumn, ' "*ZAM*" ' ) but it doesn't work. When i execute this script to see differences of like '%%' and CONTAINS (YourColumn, ' "*ZAM*" ' ) function, normally we dont expect the differences but it has.. select * from TABLE where COLUMN like '%ZAM%' except select * from TABLE where CONTAINS(COLUMN,'"*ZAM*![alt text][1]"'); UZAMA YAYI AZAMI 123ZAM896 Why this difference occurs ? Contains should find AZAMI,UZAMA or not ? Please help me , fts is very fast but it could not find all contains like '%%' operator what should I do ? [1]: /storage/temp/4266-fts.png
As described in the [documentation for the Free Text Search functions/predicates][1], the `CONTAINS` predicate looks for words, not text within words. You can use the `*` wildcard only as a suffix - ie, use `CONTAINS(YourColumn, 'ZAM*')` to indicate to FTS to return strings containing a word that starts with ZAM; there isn't a way of using FTS to find strings that have ZAM anywhere in the string. [1]:
https://docs.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search#pick-the-best-predicate-or-function
17 People are following this question.
