SQL Server 2012 Full Text Search match results

Question

I would like to know if there is a way to get the words that were matched from a full-text search using FREETEXT.

For example:

SELECT * 
FROM table 
WHERE FREETEXT(column_name, 'search term')

I'd like to be able to see which parts of the text in column_name triggered a match with 'search term' since FREETEXT returns records where the searched column are more than just the search term pattern matches as stated here in the FREETEXT/FREETEXTTABLE section.

I know that using FREETEXTTABLE can return the rank and key but I need to know which actual terms triggered a match if it is possible.

Thanks

avery_larry · Accepted Answer · 2019-09-30T18:02:35.110

1

Disclaimer, I haven't done this -- I'm going from documentation. I'm not sure this gets you what you want, but you can see how the search is tokenized: from here

After you apply a given word breaker, thesaurus, and stoplist combination in a query, you can see how Full-Text Search tokenizes the results by using the sys.dm_fts_parser dynamic management view. For more information, see sys.dm_fts_parser (Transact-SQL).

I'm guessing if you want to know which part (token) caused the match -- you'll likely need to run the search on the individual tokens and compare the result sets to see which token or tokens (search term/s) caused the given match.

edited Sep 30 '19 at 18:02

answered Sep 30 '19 at 17:57

avery_larry

2,069
1
5
17

Thanks! I was actually moving in this direction as well. And it did work using `FORMSOF`. I was able to get the list of possible words and then check to see which of those terms exist in the tokenized list of the text of interest. – Tyler Bristol Oct 01 '19 at 13:07

score 0 · Answer 2 · edited Oct 01 '19 at 06:27

0

If you want to full text search another way then use plenty of method few are written below

Select * from Table
Where Column_Name Contains('some thing')

Select * from Table
Where Column_Name exists ('some thing')

Select * from Table
Where Column_Name in ('some thing')

Select * from Table
Where Column_Name =(some) ('some thing')

edited Oct 01 '19 at 06:27

KumarHarsh

5,046
1
18
22

answered Sep 27 '19 at 21:53

Wolf

31
3

score 0 · Answer 3 · answered Sep 30 '19 at 15:45

0

You could try LIKE

SELECT * 
FROM table 
WHERE column_name like ('%search term%')

answered Sep 30 '19 at 15:45

Kelevra

116
8

KumarHarsh · Answer 4 · 2019-10-01T06:26:38.237

You will need Split String function

Then you will to create one Temporary table which is permanent.

Like in my example StagingFreeText (define data type as per requirement)

create table StagingFreeText (col varchar(100) not null)

Then create Full Text Index on StagingFreeText

In the start of script always truncate table

Truncate table StagingFreeText

Then insert the result into StagingFreeText

 ;with CTE as
(
SELECT * 
    FROM table 
    WHERE FREETEXT(column_name, 'search term')
)
insert into StagingFreeText(col)
SELECT value 
FROM CTE 
cross apply(select value from dbo.split_string(column_name,' '))ca

Again apply search condition like above

SELECT * 
FROM StagingFreeText 
WHERE FREETEXT(col, 'search term')

Note : My script is not tested but idea is very much clear.

Or if you don't want to Create another Free Text index on StagingFreeText then you can split the Search Term in #temp table and join StagingFreeText with #temp table

SQL Server 2012 Full Text Search match results

4 Answers4