Sql-server – How to perform a full-text CONTAINS call across a list of phrases

full-text-searchsql serversql-server-2008-r2t-sql

I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column.

I want to populate a table results with a record for each phrase, and the number of times that phrases was found in the description:

INSERT INTO results(phrase, foundcount)
SELECT  phrase, 
    (SELECT COUNT(*) FROM table WHERE CONTAINS(table.description, phrase))
FROM    phrases;

No matter how I try, this results in the error:

Incorrect syntax near `phrase`. Expecting STRING, TEX_LEX, or VARIABLE

I would really rather not use a cursor just so I can assign each phrase to a variable. Is that really the only option?

Best Answer

Try this:

go
create function dbo.fn_GetCountForPhrase (@phrase varchar(500))
    returns  int
as
begin
    declare @count int
    declare @localphrase varchar(8000)
    set @localphrase='"' + @phrase + '"'
    SELECT @count=COUNT(*) FROM table  a WHERE CONTAINS(table.description,   @localphrase)
    return @count
end
go
SELECT  
    phrase,  
    dbo.fn_GetCountForPhrase(phrase) 
FROM phrases

Related Solutions

Sql-server – get similar Full Text Search functionality without using Full-Text Search

Native full-text can handle all of the features you've mentioned.

Ranking/relevance, weighting
Word inflection and expansion
Customisable noise words and thesaurus

Whether or not it will be the best solution for your particular domain is a tough call. Rather than attempt to hand-roll an alternative, spike a proof of concept using SQL FTS and repeat the exercise with one or more open source alternatives.

Lucene is the obvious contender to compare and comes with the "Stack Overflow search is now 81% less crappy" endorsement of the SO team.

Sql-server – Full Text Catalog not populating

You might try scripting it out to see if you get a different behaviour. This simple example runs in under a minute on my local SQL 2005 instance:

USE master
GO

SET NOCOUNT ON
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
    ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo3
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO

ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO

USE fullTextDemo3
GO

CREATE TABLE dbo.nearnessTest (
    rowId           INT IDENTITY,
    keywords        VARCHAR(MAX) NOT NULL,
    notes           VARCHAR(100) NOT NULL,

    CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO


INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
--  CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
  --AND m2.language_id = 1033
GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' ) 
CREATE FULLTEXT CATALOG ftc_List3
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT 
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT * 
FROM dbo.nearnessTest 
WHERE CONTAINS( keywords, 'error' )

What happens when you issue an explicit start for the index population, eg

ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;

Run these summary functions

SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus

Best Answer

Related Solutions

Sql-server – get similar Full Text Search functionality without using Full-Text Search

Sql-server – Full Text Catalog not populating

Related Question