SQL 2019 Full text search on multiple column with multiple values

Mathias Werner 1 Reputation point
2020-12-09T16:29:49.527+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,583 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Mathias Werner 1 Reputation point
    2020-12-09T16:33:21.057+00:00

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

    Thanks,

    0 comments No comments

  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2020-12-10T08:16:39.143+00:00

    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.

    0 comments No comments

  3. Mathias Werner 1 Reputation point
    2020-12-16T09:58:17.267+00:00

    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

    0 comments No comments