Use full text search Ms SQL SERVER to match words
I need to write a query in sql using full text that returns the records in word order count
exmaple: in the database
row 1 = "brown cow" //1 match
row 2 = "lazy dog" //2 matches
User input: "A quick brown fox jumps over a lazy dog"
both inputs will return with line 2 at the top
I can't see anything that initially does this. You may need to create a function that compares the results to your search string to determine how many matches exist.
You can then pipe the results of the free text search to this function, which will return a hit counter. Finally, you must order the results by the number of matches.
a source to share
In MS SQL, for a guerrilla 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 a table with two of your example rows, and MATCHCOLUMN is the name of a column with text.
However, performance with this approach will not be similar to full text search.
a source to share
Given the following assumptions:
- Your table already includes the full text.
- Your table is named "example" and contains 2 columns named "exampleID" and "exampleText" (exampleText in your full text index)
- You can parse the user input as shown for each example:
parsedUserInput = '', 'quick', 'brown', 'fox', 'jump', 'over', 'the', 'lazy', 'dog'
Then I think you are looking for 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 there might be a rank for you ...
parsedUserInput = "OR" fast "OR" brown "OR" fox "OR" jumping "OR" over "OR" 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
a source to share