0

İ 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. Please help me , fts is very fast but it could not find all contains like '%%' operator what should I do ?

Mehmet
  • 1
  • 1
  • 1

2 Answers2

0

SQL Server Fulltext Search (Version 2022) does not support postfix search, like '"*zam"'.

https://dba.stackexchange.com/a/230067

https://learn.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search

ibram
  • 4,414
  • 2
  • 22
  • 34
-1

You can use "*" before in contain syntax same as like operator . but you need to use double quote before and after the search string. Try this query once.

SELECT *
FROM YourTable
WHERE CONTAINS(YourColumn,'"*ZAM*"');

(OR)

select * from YourTable where YourColumn like '%ZAM%'
Mahesh.K
  • 901
  • 6
  • 15
  • Hi thanks for helping but I have written wrongly first question, I have tried also with CONTAINS(YourColumn,'"*ZAM*"'); but it does not find all Words that contains ZAM like ZAMANLAMA and AZAMI , or i tried this exam CONTAINS(YourColumn,'"*APU*"'), it did not find KAPUT word , is it possible that this is bug of fts ? – Mehmet Aug 28 '17 at 18:25
  • Hi, when i write * star character in the comment, it does not show you i dont know why, but i am using star character in contains function like CONTAINS(YourColumn,'"*ZAM*"') – Mehmet Aug 28 '17 at 18:27
  • When i execute this script to see differences of like '%%' and contains 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*"'); UZAMA YAYI AZAMI 123ZAM896 Why this difference occurs ? Contains should find AZAMI,UZAMA or not ? – Mehmet Aug 28 '17 at 18:33
  • @Mehmet use `CONTAINS(YourColumn,'"*Yourstring*"')` and try .i mean to say place star * before and after YourSubstring and try ,not just `''YourSubstring''` – Mahesh.K Aug 29 '17 at 05:08
  • Hi, i have used star *character before and after string it is invisible in comment place but i have used, eventhough i have used star character before and after string , it could not find the word that contains string – Mehmet Aug 29 '17 at 10:57
  • BOS2004650030UZAMA, AZAMI is for ZAM example, KAPUT is for APU example. Star character is running for right side of word, left side of word is not running. Can you help me, is this bug ? – Mehmet Aug 29 '17 at 14:26