Hmmm . . . One method is to summarize the matches by word and columns and then use that information to filter and order:
SELECT c.*
FROM Customers c CROSS APPLY
(SELECT COUNT(DISTINCT word) as num_matched_words,
SUM(CASE WHEN ColName = 'CustomerName' THEN 1 ELSE 0 END) as customername_matches,
SUM(CASE WHEN ColName = 'ContactName' THEN 1 ELSE 0 END) as contactname_matches,
SUM(CASE WHEN ColName = 'Address' THEN 1 ELSE 0 END) as addressname_matches
FROM (VALUES ('CustomerName', @1, CASE WHEN CustomerName LIKE @1 THEN 1 ELSE 0 END),
('CustomerName', @2, CASE WHEN CustomerName LIKE @2 THEN 1 ELSE 0 END),
('CustomerName', @3, CASE WHEN CustomerName LIKE @3 THEN 1 ELSE 0 END),
('ContactName', @1, CASE WHEN ContactName LIKE @1 THEN 1 ELSE 0 END),
('ContactName', @2, CASE WHEN ContactName LIKE @2 THEN 1 ELSE 0 END),
('ContactName', @3, CASE WHEN ContactName LIKE @3 THEN 1 ELSE 0 END),
('Address', @1, CASE WHEN Address LIKE @1 THEN 1 ELSE 0 END),
('Address', @2, CASE WHEN Address LIKE @2 THEN 1 ELSE 0 END),
('Address', @3, CASE WHEN Address LIKE @3 THEN 1 ELSE 0 END)
) v(ColName, Word, IsMatch)
WHERE IsMatch
) v
WHERE num_matched_words = 3
ORDER BY customername_matches DESC,
contactname_matches DESC,
addressname_matches DESC;
that works :D i still need to loop through the search terms to generate the SQL query, but I guess a working suggestion is much, much better than nothing. do you know anything about how slow this would be?