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 directory in 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.

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

red
blue
blueberry
yellow, blue

I want to use the search for "red blue" and return all the above lines.

Is it possible?

Answer 1

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