Hi

I've seen lots of posts on this subject but no satisfactory answers. I asked
the question in 2004 before SQL 2005 arrived so I wondered if there is a
neat solution to this problem now. I want a way to get return one row from
the query below.

CREATE TABLE Users
(
UID int IDENTITY(1000,1) PRIMARY KEY,
FirstName varchar(50) DEFAULT '' NOT NULL,
LastName varchar(50) DEFAULT '' NOT NULL
)

INSERT INTO Users(FirstName, LastName) VALUES('John', 'Smith')
INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Smith')
INSERT INTO Users(FirstName, LastName) VALUES('John', 'Brown')
INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Brown')

SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John AND Smith')

The problem with this technique below is I don't know how many words are
going to be searched.

SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John') and CONTAINS (*, 'Smith')

I thought about concatenating columns in a view but I can't see how I can
get that to work. FREETEXT gives me two many answers too. Someone suggested
a trigger which adds the concatenated columns to another text column for
full-text indexing. It seems like it might be the best option at the moment
but it seems messy to me.

Thanks
Andrew