0

New to SQL for the most part so perhaps there is a better approach but I'm looking for a way to search through a database using a textbox which can take any number of words/numbers. Basically like a search engine. Columns to be searched: [Col1], [Col2], and [Col3]

I have the table Full-Text indexed, is there a way I can use CONTAINS for this?

DECLARE @Search nvarchar(500)
SET @Search = 'foo 7 bar' -- can have multiple words/numbers

SELECT *
FROM TableName
WHERE CONTAINS([Col1] or [Col2] or [Col3], @Search)

I realize the last line syntax is incorrect but that's basically what I'm looking for. Shouldn't be exact match or case sensitive, nor should the order of wording matter ('red cat' = 'cat red'). Also would like to show results for close matches, i.e., some of the keywords don't match anything.

Thank you!

MuffinMan
  • 65
  • 1
  • 10
  • This should give you hint. http://stackoverflow.com/questions/3441775/use-multiple-words-in-fulltext-search-input-string – Chetan Feb 18 '17 at 02:01

2 Answers2

1

Your each search word must be surrounded in quotes..

'"Hi" AND "I" AND "am" AND "Yogeesha"'

For more information, Have a look at https://msdn.microsoft.com/en-US/library/ms187787(v=SQL.90).aspx

YOGEESHA NAIK
  • 73
  • 1
  • 9
0

Turns out, using FREETEXT() gives a close result to what I've been looking for.

MuffinMan
  • 65
  • 1
  • 10