SQL Server 2008 - Full Text Search

Question

I have a table:

WID          WName
2          Looking for Data
6          What data is here
7          Nothing
8          Here we go again

I created a Full Text Catalog on the WName Column.

When I use:

SELECT * FROM Workspace WHERE CONTAINS (WorkspaceName, ' "data* here*" ') -- Nothing 
SELECT * FROM Workspace WHERE FREETEXT (WorkspaceName, 'data*') -- Row ID: 2,6
SELECT * FROM Workspace WHERE FREETEXT (WorkspaceName, 'here*')  -- Nothing 
SELECT * FROM Workspace WHERE FREETEXT (WorkspaceName, '*ere*')  -- Nothing 
SELECT * FROM Workspace WHERE FREETEXT (WorkspaceName, 'here')  -- Nothing 
SELECT * FROM Workspace WHERE FREETEXT (WorkspaceName, 'data') --Row ID: 2,6

These are not the results that I expected.

What I want to be able to do is: If I had a color column with values

red
blue
blueberry
yellow, blue

I want to use a search of 'red blue' and have all of the above rows come back.

Is that possible?

Answer 1

SELECT  *
FROM    workspace
WHERE   CONTAINS(WorkspaceName, '"red*" OR "blue*"')