CODEWITHSUNDEEP
sql-serverfull-text-searchcontains
André Reichelt
André Reichelt

Reputation: 1631

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.

Upvotes: 0

Views: 148

Answers (2)

André Reichelt
André Reichelt

Reputation: 1631

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.

Upvotes: 0

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

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 CONTAINS((Name1, Name2, Street, Number, ZipCode, City, Country), '742*')

Note:- You can try this, by separating both the value....and i'm don't know column table value containing 'Bier*' or only 'Bier'

Upvotes: 1

Related Questions