none
FullText keyword match

    Question

  • Hi

    I am trying to find exact matches for keywords in resumes using fulltext queries.

    I am using following queries.  Is it possible to find which keyword(searchterm) matched the search ?

    I am looking for an optimized way of doing this.

    TIA

    Expected Result
    =====================

    LastName             KeywordMatched
    Smith                  New York
    Doe                     Dallas
    Henry                 Chicago


    GO
    EXEC sp_fulltext_database enable
    GO
    SET NOCOUNT ON
    CREATE TABLE SearchTerm (ID int Identity(1,1),Term nvarchar(100))
    CREATE TABLE ApplicantResume(ID int Identity(1,1) constraint PK_ApplicantResume primary key (ID) NOT NULL,FirstName varchar(25), LastName varchar(25),ResumeText varchar(Max))

    CREATE FULLTEXT CATALOG MySearchCatalog WITH ACCENT_SENSITIVITY = ON AUTHORIZATION [dbo]
    CREATE FULLTEXT INDEX ON [dbo]. ApplicantResume (ResumeText) KEY INDEX PK_ApplicantResume ON MySearchCatalog WITH CHANGE_TRACKING AUTO

    insert into ApplicantResume VALUES('N','Smith','I live in New York')
    insert into ApplicantResume VALUES('D','Doe','I live in Dallas and work in Chicago')
    insert into ApplicantResume VALUES('C','Henry','I live in Chicago')


    INSERT SearchTerm VALUES ('New York')
    INSERT SearchTerm VALUES ('Dallas')
    INSERT SearchTerm VALUES ('Chicago')

    --It takes sometime to populate fulltext index. Run this query again.
    SELECT FT.[Rank], FirstName,LastName FROM ApplicantResume  R LEFT OUTER JOIN
    CONTAINSTABLE(ApplicantResume,ResumeText,'"New York" OR "Dallas" OR "Chicago" OR "Los Angeles" ')  FT ON R.ID = FT.[KEY]

    • Moved by Kalman Toth Thursday, August 22, 2013 6:59 AM Not t-sql
    Wednesday, August 21, 2013 7:29 PM

All replies

  • Have a look at sys.dm_fts_index_keywords_by_document and  sys.dm_fts_parser, eg

    SELECT * FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('ApplicantResume') ) kbd
    SELECT * FROM sys.dm_fts_parser ('"New York" OR "Dallas" OR "Chicago" OR "Los Angeles" ', 1033, 0, 0)
    

    Thursday, August 22, 2013 12:25 AM
  • Thank You!

    I have following questions

    1. Please see queries a) and b) below. Will a) be faster than b) as we are providing dm_fts_parser function all the   search terms in a single string.

    2. Based on my research these are documented and supported by Microsoft. Please confirm.

    3.  In what situations would anyone use these dynamic views rather than using CONTAINS,CONTAINSTABLE,FREETEXT

    TIA

    Queries

    ======

    a) 

     DECLARE @SearchTerms varchar(200)
     SET @SearchTerms =''
     SELECT @SearchTerms = @SearchTerms+ '"'+ TERM +'" OR '  FROM SearchTerm
     SET @SearchTerms = Substring(@SearchTerms,1,LEN(@SearchTerms)-3)
     SELECT  Keyword,source_term,*  FROM sys.dm_fts_parser (@SearchTerms, 1033,0,0)

    b)
    SELECT  Keyword,source_term FROM SearchTerm T  CROSS APPLY sys.dm_fts_parser ('"' + t.Term + '"', 1033,0,0)

     

     

     

    Thursday, August 22, 2013 3:15 AM
  • In answer to your questions:

    1) Will it be faster?  You tell us.  Try it with your data.

    2) Yes these are fully documented: sys.dm_fts_index_keywords_by_document and sys.dm_fts_parser.

    3) The DMVs offer extra / different functionality to the full-text functions, so basically use them when you need to.

    Good luck!

    Thursday, August 22, 2013 9:10 AM