Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Michael Coles A Google-like Full Text Search RE: A Google-like Full Text Search

  • October 7, 2008 at 5:15 pm

    #882045

    Martin Nyborg (10/7/2008)

    Thanks for the great article. As soon as I read it. I started to implement full text search on our most used table.

    I have altered the code to use CONTAINSTABLE(tblsite,*,@ftsQuery) because I want to be able to search in many fields.

    But it is not working.

    I have this field list with full text index on (Site (PK), SiteName, Address, HouseNumber, Zip, City)

    I can search for "Vejle" (danish city) and I get result from columns SiteName and Address, so that works.

    Now I want to search for "Vejle" and "17". I want to find the city "vejle" and all streets with housenumber "17" but the result set is empty. Can any one help me out on this? I think I have tried all search combination's

    The problem you're encountering is that you're searching for ("17" AND "Vejle"), and iFTS stipulates that they must exist together in the same column. In order to search for "17" in one column and "Vejle" in another column, you must create two FTS predicates ANDed together like this:

    CONTAINS (tblsite, HouseNumber, "17")

    AND CONTAINS (tblsite, City, "Vejle")

    This can be done, but adds considerable complexity to the query creation.

    Thanks

    Mike C