Search code examples
sql-serverfull-text-searchcontains

SQL Server CONTAINS doesn't work as expected

I have an address table, and I want to search over that with the free text function CONTAINS.

SELECT [ID]
      ,[ID_AddressType]
      ,[Name1]
      ,[Name2]
      ,[Street]
      ,[Number]
      ,[ZipCode]
      ,[City]
      ,[Country]
  FROM [TrailerLoadingAssistant].[dbo].[Address]
  WHERE CONTAINS((Name1, Name2, Street, Number, ZipCode, City, Country), '"Bier*" AND "742*"')

In my table, there is an entry, where the street name starts with Bier and the zip code with 742. Why doesn't the script find the entry? If I replace the AND with an OR, it works, but it also finds other entries, where only one of the search terms is fulfilled.

Solution

  • I solved the problem by adding a calculated column to my Address table, as suggested here. That contains all relevant fields. The full text index now only consideres this virtual column.