What I want to be able to do is send a query into a full-text search that returns only the results that match ALL words. The default implementation of full-text search seems to match ANY words in the query, such that adding more search terms returns more results, instead of refining the results.
I am using a stored procedure to perform a search on a giant table (>1 million records). I couldn't get Full-Text Search to work the way I wanted out-of-the-box (because of the ANY search nonsense), so I used a little trickery with sys.dm_fts_parser to customize tokenization of the search term and create a query using "AND" between all the words (to require ALL words to be matched). This seemed like a good idea, since it's doing exactly what I need it to do, but there's a problem: selecting from sys.dm_fts_parser requires sysadmin privileges, which I do not have on the production environment.
Is there any way to perform an ALL words search in ANY order (not necessarily using Full-Text Search, but this seems to be the fastest built-in implementation), but without needing special permissions?
DECLARE @query AS varchar(210), @inputQuery AS varchar(210)
DECLARE @tokens AS TABLE (display_term varchar(255))
-- split the search term into "words", according to the FULLTEXT stoplists
INSERT INTO @tokens
SELECT display_term
FROM sys.dm_fts_parser('"' + @input + '"', 1033, 0, 0) -- double quotes are required around the input search term
WHERE special_term = 'Exact Match' -- discard any irrelevant search terms, such as "and", "or", "the", etc.
-- create a query consisting of "word1 AND word2 AND word3 AND..." etc
SET @query = STUFF(
(SELECT ' AND ' + display_term
FROM @tokens
FOR XML PATH ('')), 1, 5, '')
SET @inputQuery = '"' + @input + '"'
DECLARE @ResultPersons AS TABLE (perPKID int)
INSERT INTO @ResultPersons (perPKID) (
SELECT perPKID FROM Persons WHERE CONTAINS(perNameComputed, @inputQuery) -- exact matches go to the top of the results list
UNION
SELECT perPKID FROM Persons WHERE CONTAINS(perNameComputed, @query)
)
