Full Text Search Multiple Columns

  • we are testing a new project and are trying to use Full Text search.

    We have a front end app that allows customer service staff to search for customers. They have the ability to search up to 10 different fields of data. (I.e. First name, Last name, Address, etc)

    We setup a Full Text index on the columns possible.

    Now we are having trouble getting the query to return the correct results.

    Several attempts have been made using freetext and contains and results will not return.

    For FREETEXT with multiple columns listed and then passing in a concatenated variable of all input values appears to return an OR result set and we cannot see how to force an AND. Example we pass in a first and last name we get all records with the first name or the last name. So if John Smith is entered we get back all Johns and all Smiths. We want just John Smiths returned.

    We tried using CONTAINS in the WHERE clause with all 10 fields. The query does not return good results if any of the 10 fields are blank or NULL. We tried putting an ISNULL and it did not work.

    Our conclusion is we might have to build a SP that builds the WHERE clause dynamically and puts the CONTAINS for only input fields that have data.

    If anyone has any other suggestions I would like to hear them.

    Thanks

    gb

  • I don't use FULL TEXT, but this isn't the first time I've heard of this sort of issue. Could you post some DDL, sample data, and the querying code you've tested so we can better assist you in resolving this issue?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • ------------------------------------------------------

    -- FULL TEXT QUERY FREETEXT

    ------------------------------------------------------

    --from gs_Subscriber where freetext((FirstName,LastName,GSCustomerNumber,Phone), '"%Juan Gomez%"')

    declare

    @FirstName nvarchar(25) = NULL,

    @LastName nvarchar(25) = NULL,

    @Email nvarchar(256) = NULL,

    @Phone nvarchar(25) = NULL,

    @Address1 nvarchar(30) = NULL,

    @City nvarchar(20) = NULL,

    @ZipCode nvarchar(20) = NULL,

    @CardNumber nvarchar(20) = NULL,

    @GSCustomerNumber nvarchar(20) = NULL

    set @FirstName = 'juan'

    set @LastName = 'gomez'

    set @Email = null

    set @Phone = null

    set @Address1 = '3222 W AGUSTA BLVD'

    set @City = null

    set @ZipCode = null

    set @CardNumber = null

    set @GSCustomerNumber = null

    DECLARE @SubscriberSearch VARCHAR(1000)

    SET @SubscriberSearch = @FirstName +' '+ @LastName +' '+ @Phone +' '+ @GSCustomerNumber

    ;WITH cte AS

    (

    SELECT ROW_NUMBER() OVER(PARTITION BY s.SubscriberID ORDER BY s.SubscriberID, ea.IsActive DESC, a.IsActive DESC) AS RowNum,

    s.SubscriberID, s.FirstName, s.LastName,

    ea.EmailAddress, ea.IsActive as EmailIsActive,

    s.Phone,

    a.Address1, a.City, a.ZipCode, a.IsActive as AddressIsActive,

    sc.CardNumber, s.GSCustomerNumber

    FROM gs_Subscriber s

    LEFT JOIN gs_EmailAddress ea ON s.SubscriberID = ea.SubscriberID

    LEFT JOIN gs_Address a ON s.SubscriberID = a.SubscriberID

    LEFT JOIN gs_Subscription sc ON s.SubscriberID = sc.SubscriberID

    WHERE freetext((s.FirstName,s.LastName,s.GSCustomerNumber,s.Phone), @SubscriberSearch)

    --AND contains(s.LastName, @LastName)

    --AND contains(s.Phone, @Phone)

    --AND contains(ea.EmailAddress, @Email)

    --AND contains(a.Address1, @Address1)

    --AND contains(a.City, @City)

    --AND contains(a.ZipCode, @ZipCode)

    --AND contains(sc.CardNumber, @CardNumber)

    --AND contains(s.GSCustomerNumber, @GSCustomerNumber)

    )

    SELECT SubscriberID, FirstName, LastName,

    EmailAddress, EmailIsActive, Phone,

    Address1, City, ZipCode, AddressIsActive,

    CardNumber, GSCustomerNumber

    FROM cte

    WHERE RowNum = 1

    ORDER BY SubscriberID, EmailIsActive, AddressIsActive

  • We need your table DDL (CREATE TABLE statements) and some sample data to test with, please.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • This pattern works well in these cases:

    ...and (@firstname is null or contains(firstname, @firstname))

    ...

    Option (recompile)

  • Here's some code I've used. I can't take full credit for the code and apologies to whoever wrote it originally. I've had it in my "toolbox" a long time.

    First the parser:

    CREATE PROCEDURE [dbo].[FullTextParseSearchString]

    (

    @keywords VARCHAR(500) = NULL

    ,@proximity VARCHAR(10) = 'NEAR' -- and, or, near

    ,@parsedstring VARCHAR(500) OUTPUT

    )

    AS

    BEGIN

    SET CONCAT_NULL_YIELDS_NULL OFF

    SET NOCOUNT ON

    /*

    DECLARE

    @tempstring NVARCHAR(4000)

    ,@ParsedSearchString NVARCHAR(4000)

    EXEC dbo.FullTextParseSearchString

    'Red Car'

    ,'NEAR'

    ,@tempstring OUTPUT

    SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')'

    SELECT @ParsedSearchString AS ParsedResult

    */

    DECLARE

    @sql VARCHAR(2000)

    ,@where VARCHAR(1000)

    ,@i INT -- old posn in string

    ,@j INT -- match posn in string

    ,@k INT -- new start posn

    ,@len INT -- string length

    ,@search VARCHAR(500)

    ,@search2 VARCHAR(500)

    ,@fuzzy VARCHAR(1000)

    ,@keyword VARCHAR(500)

    ,@synonyms VARCHAR(255)

    ,@new_keywords VARCHAR(1000)

    ,@found INT

    SET @keywords = LTRIM(RTRIM(@keywords))

    SET @keywords = REPLACE(@keywords,'*',' ')

    SET @keywords = REPLACE(@keywords,' AND ',' ')

    SET @keywords = REPLACE(@keywords,' OR ',' ')

    SET @keywords = REPLACE(@keywords,'"',' ')

    SET @keywords = REPLACE(@keywords,'(',' ')

    SET @keywords = REPLACE(@keywords,')',' ')

    SET @keywords = REPLACE(@keywords,'o''','')

    -- mcdonald's ==> mcdonald''s, escape out the ' which is a valid piece of punctuation

    SET @keywords = REPLACE(@keywords,'''','''''')

    -- fix é type characters

    SET @found = CHARINDEX('é',@keywords)

    IF @found > 0

    IF @found = LEN(@keywords)

    SET @keywords = REPLACE(@keywords,'é','')

    ELSE

    SET @keywords = REPLACE(@keywords,'é ','* ')

    -- set proximity type

    IF @proximity IS NULL

    OR @proximity = ''

    SET @proximity = 'NEAR'

    SELECT

    @i = 1 -- start of the first string

    SELECT

    @j-2 = 0 -- end of the first string unknown

    SELECT

    @len = LEN(@keywords)

    SELECT

    @search = ''

    SELECT

    @fuzzy = ''

    WHILE (@j <= @len)

    BEGIN

    SELECT

    @k = @j-2 + 1 -- New start posn

    -- cut up the string into its individual words, split this

    -- into its inflectional part and standard FTI search portion

    SELECT

    @j-2 = CHARINDEX(' ',@keywords,@k)

    -- Finish if no more found

    IF (@j <= 0)

    BEGIN

    -- Add the last part of the string

    SELECT

    @search = @search + '"'

    + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1)))

    + '*"'

    SELECT

    @search2 = @search2 + '"'

    + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1)))

    + '"'

    IF NOT SUBSTRING(@keywords,@k,@len - @k + 1)

    IN ('of','and','at','a','the','&','it','for')

    SELECT

    @fuzzy = @fuzzy

    + 'FORMSOF(INFLECTIONAL,"'

    + LTRIM(RTRIM(SUBSTRING(@keywords,@k,@len - @k + 1)))

    + '")'

    BREAK

    END

    IF (@j < @len)

    BEGIN

    IF (SUBSTRING(@keywords,@j - 1,1) <> ' ')

    BEGIN

    SELECT

    @search = @search + '"'

    + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))

    + '*" ' + @proximity + ' '

    SELECT

    @search2 = @search2 + '"'

    + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))

    + '" ' + @proximity + ' '

    IF NOT SUBSTRING(@keywords,@i,@j - @i)

    IN ('of','and','at','a','the','&','it','for')

    SELECT

    @fuzzy = @fuzzy

    + 'FORMSOF(INFLECTIONAL,"'

    + LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))

    + '") AND '

    SELECT

    @i = @j-2 + 1

    END

    END

    END

    IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 2,3) = ' OR'

    SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 2)

    IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 3,4) = ' AND'

    SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 3)

    -- Example: ("mcdonalds") OR ("mcdonalds*") OR (FORMSOF(INFLECTIONAL,"mcdonalds"))

    SELECT

    @search = '"' + @keywords + '" OR ' + '(' + @search2

    + ') OR (' + @search + ') OR (' + @fuzzy + ')'

    SET @parsedstring = @search

    SET CONCAT_NULL_YIELDS_NULL ON

    END

    Now the actual search:

    CREATE PROCEDURE [dbo].[FullTextSearch]

    @SearchTerm NVARCHAR(4000)

    ,@MinRelevance INT

    ,@MaxRows INT

    ,@NumRows INT = 0 OUTPUT

    AS

    BEGIN

    /* Requires procedure FullTextParseSearchString to parse the search term(s) */

    SET NOCOUNT ON

    SET @SearchTerm = ISNULL(@SearchTerm,'')

    SET @MinRelevance = ISNULL(@MinRelevance,10)

    SET @MaxRows = ISNULL(@MaxRows,50)

    DECLARE

    @proximity VARCHAR(10)

    ,@tempstring NVARCHAR(4000)

    ,@ParsedSearchString NVARCHAR(4000)

    SET @SearchTerm = REPLACE(REPLACE(REPLACE(REPLACE(@SearchTerm,'%',''),'"',''),'+',''),'&','')

    SET @ParsedSearchString = '1=1 '

    IF CHARINDEX(' OR ',@SearchTerm) > 0

    SET @proximity = 'OR'

    ELSE IF CHARINDEX(' AND ',@SearchTerm) > 0

    SET @proximity = 'AND'

    ELSE

    SET @proximity = 'NEAR'

    IF LTRIM(RTRIM(@SearchTerm)) <> ''

    BEGIN

    EXEC dbo.FullTextParseSearchString

    @SearchTerm

    ,@proximity

    ,@tempstring OUTPUT

    SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')'

    END

    --for testing

    --SELECT @proximity

    --SELECT @ParsedSearchString

    SET ROWCOUNT @MaxRows

    SELECT

    ROW_NUMBER() OVER (ORDER BY k.[RANK] DESC,a.PublishDate,a.Title) AS RowNum

    ,a.ArticleID

    ,a.Title

    ,a.BodyContent

    ,a.Author

    ,a.PublishDate

    ,k.[RANK] AS Relevance

    FROM

    dbo.Articles AS a

    INNER JOIN

    CONTAINSTABLE

    (

    dbo.Articles

    ,(*)

    ,@ParsedSearchString

    )

    AS k

    ON a.ArticleID = k.

    WHERE

    k.[RANK] > @MinRelevance

    AND a.isActive = 1

    ORDER BY

    k.[RANK] DESC

    ,a.PublishDate

    ,a.Title

    SET ROWCOUNT 0

    END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply