SQL 2019 Full text search on multiple column with multiple values

Question

Hello,

I want to us full text search for cross column searching of multiple search values. For example there is a address table containing the column names: Id, Name, City, Street. This table is filled with proper test data and full text index is implemented.

The search with a parameter with CONTAINS and CONTAINSTABLE is working fine for one search value over all columns. Also the 'OR-logic' is working as expected. But if I want to use the 'AND-logic' I got 0 records back. Although there are records. Search string is '"Smith" AND "New York"' or 'Smith AND New York' Both is not working.
Search string '"Smith" OR "New York"' has a result set but this is not effective.
I have seen this behaviour on SQL 2016,2017 fully patched. The AND-logic is only working on one column. E. g. the city name is Washington South and the search string is 'Washington AND South' you get you record. But if 'South' is in another column e. g. Street then you have lost.

I am thankful for any ideas.

Regards,
Mathias

Testscript:

Declare @検索君 nvarchar(1000) = N''

Set @検索君 = N'"Smith" OR "New York"'

Select
tt.*
From [dbo].[tbl_TestTable_FT] tt
Where CONTAINS (*,@検索君 )

Select
tt.*
,ct.Rank
From [dbo].[tbl_TestTable_FT] tt
Inner Join CONTAINSTABLE (dbo.tbl_TestTable_FT,*,@検索君 ) ct
On ct.[KEY] = tt.id
Order By
ct.Rank Desc

Answer 1

After the upload I recognized that the wildcards in the contains segments for the columns is disappeared. Please enter *

Thanks,

Answer 2

Hi @Mathias Werner ,

To query multiple columns, you can try to specify a list of columns in contains ().

For example:

SELECT Name, Color     
FROM Production.Product    
WHERE CONTAINS((Name, Color), 'Red AND Blue');   

Please refer to CONTAINS which might help.

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi AmeliaGu-msft,

thank you for your response. I just tried again to specify a list of columns.
But with no success. The result set is empty. Only if I enter one column name
I get a result.

Regards,
Mathias