Use Ms SQL SERVER full text search to match words

1
votes

I need to write a query in sql using full text that returns records in the order of matching words count

exmaple: in data base

row 1 = "brown cow" //1 match
row 2 = "lazy dog" //2 matches

User input: "The quick brown fox jumps over the lazy dog"

both inputs would be return with row 2 on the top

3
sql-serverfull-text-search
Can you maybe expand on this with a table-create script, insert-data script, and the results you expect to get? I'm having trouble understanding what you're after, and judging by the other answers, other people are having some difficulty too. Thanks! – Brent Ozar

3 Answers

1
votes

I don't see anything that natively does this. You might have to create a function which compares the results to your search string in order to determine how many matches exist.

You could then pass the results of the free text search into that function which would return a match count. Finally, you'd order the results by the match count.

1
votes

In MS SQL, for a guerilla approach you can do something like this:

select * from MATCHTABLE where patindex('%'+MATCHCOLUMN+'%','The quick brown fox jumps over the lazy dog')>0

where MATCHTABLE is the table with your two example rows, and MATCHCOLUMN is the column name with the text.

However, performance with this approach will not be anything like fulltext searching.

1
votes

Given the following assumptions:

  1. Your table is already full text enabled
  2. Your table is named "example" and contains 2 columns called "exampleID" and "exampleText" (exampleText is in your fulltext index)
  3. You are able to parse the user input as shown for each example:

parsedUserInput = 'The','quick','brown','fox','jumps','over','the','lazy','dog'

Then I think what you're looking for is something like this: 

SELECT display_term as term
,document_count as numberOfOccurences 
FROM sys.dm_fts_index_keywords (db_id(),object_id('example'))
WHERE display_term IN({parsedUserInput})

Otherwise maybe a rank would work for you...

parsedUserInput = "The" OR "quick" OR "brown" OR "fox" OR "jumps" OR "over" OR "the" OR "lazy" OR "dog"

SELECT 
ranks.rank AS rank
,e.exampleID
FROM example e
INNER JOIN CONTAINSTABLE (  example, exampleText, ' **{parsedUserInput}** '   ) AS ranks
    ON e.exampleID = ranks.[KEY]
WHERE ranks.rank > 0 -- return only rows that have a rank
order by ranks.rank desc