Post reply

FULL TEXT search and LIKE operator problem

  • November 13, 2009 at 3:36 am

    #136908

    i have a full text index on a column

    The rows returned by the below two query are different

    help me out , i need same resutls for both query

    Select * from emp where name like '%varun%'

    Select * from emp where Freetext(name , 'varun')

  • November 13, 2009 at 4:01 am

    #1078804

    Has your Full-Text Catalog been fully built?

    Are you able to provide some sample data showing the different results from the queries?

  • November 13, 2009 at 4:06 am

    #1078805

    The First Query gave me 1734 rows

    using FREETEXT gave me just 24 rows

    kinldy guide me how to check whether the catlog is build fully or not.

    the catlog size is abt 300 mb for a 4 gb table.

    i cannot show actual results due to dataclassification

  • November 13, 2009 at 4:11 am

    #1078806

    You can right-click on the catalog and click properties and under the population status - it will say 'populating catalog' if the catalog is building.

  • November 13, 2009 at 4:21 am

    #1078810

    No , its not showing any status . shows only rebuild.

    any guess abt this problem

  • November 13, 2009 at 4:34 am

    #1078817

    Asuuming that this is on a development sever - you could try and rebuild the catalog

  • November 13, 2009 at 4:46 am

    #1078825

    no improvement

  • November 13, 2009 at 5:04 am

    #1078836

    Are those queries in the first post the actual queries or have you modified them?

    The big difference between Like and FreeText is that FreeText will search for whole words, wheras Like will search for words that contain part of the search string.

    For example if name = 'Varune'

    Select * from emp where name like '%varun%'

    Select * from emp where Freetext(name , 'varun')

    then the FreeText will not return any results as it is not a word match

    So you will be getting more results with Like as this is returning partial matches.

    try running

    Select * from emp where name like '% varun %'

    to see if you get the same result as the Full-Text Query.

    Though it is hard to give more specific advice without seeing any test data.

  • November 13, 2009 at 5:14 am

    #1078844

    The table names are changed

    our requirment is

    the production table is large around 40 million rows

    the current query uses a like search which takes huge amount of time to give output

    we thought of moving into Full text index to make it fast .

    we need the exact implement the like '%varun%' search using full text search

  • November 13, 2009 at 5:26 am

    #1078847

    If you want the same behaviour then use;

    Select * from emp where CONTAINS(name , '"*varun*"')

    you may also want to have a look at using CONTAINS for Full-Text searches as it can be customised a lot more than FREETEXT

Viewing 10 posts - 1 through 9 (of 9 total)

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