SQL Server – Extract Tokens from String Using Full-Text Search

full-text-searchsql serversql-server-2008-r2

What is the query to get the tokens in a string from SQL Server 2008 R2?

I need it to highlight results of FREETEXT

Best Answer

You can use DMV sys.dm_fts_index_keywords_by_document to list the words for a given document, eg something like this:

SELECT DISTINCT display_term
FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.Users') ) X

I had a play with this product a few years ago that does hit-highlighting, it was quite nice but I didn't go past the 30-day trial.

Related Solutions

Sql-server – viewing the population? (sql server full text search)

I think you need to be having a look at the Full Text DMVs here: http://msdn.microsoft.com/en-us/library/ms174971%28v=sql.105%29.aspx

Specifiably I think you want something like:

SELECT * FROM sys.dm_fts_index_keywords(db_id('AdventureWorks2012'), object_id('HumanResources.JobCandidate'))
GO

SELECT * FROM sys.dm_fts_index_keywords_by_document(db_id('AdventureWorks2012'), object_id('HumanResources.JobCandidate'))
GO

Sql-server – Prefix full text search with numbers in SQL Server

It seems the problem is caused by 8 (and 0,1,2,3,4,5,6,7 and 9) being on the stopword list.

A workaround is to base the table on an empty stopword list (or at least one without the 1-digit numbers):

CREATE FULLTEXT STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test SET STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;

Best Answer

Related Solutions

Sql-server – viewing the population? (sql server full text search)

Sql-server – Prefix full text search with numbers in SQL Server

Related Question