FULL TEXT search and LIKE operator problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » FULL TEXT search and LIKE operator problem

FULL TEXT search and LIKE operator problem

Rate Topic

Display Mode

Topic Options

Author

Message

varunragul

Posted Friday, November 13, 2009 3:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:59 AM
Points: 45, Visits: 230

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')

Post #818352

steveb.

Posted Friday, November 13, 2009 4:01 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

Has your Full-Text Catalog been fully built?

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

Post #818364

varunragul

Posted Friday, November 13, 2009 4:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:59 AM
Points: 45, Visits: 230

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

Post #818365

steveb.

Posted Friday, November 13, 2009 4:11 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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.

Post #818366

varunragul

Posted Friday, November 13, 2009 4:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:59 AM
Points: 45, Visits: 230

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

any guess abt this problem

Post #818371

steveb.

Posted Friday, November 13, 2009 4:34 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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

Post #818380

varunragul

Posted Friday, November 13, 2009 4:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:59 AM
Points: 45, Visits: 230

no improvement

Post #818390

steveb.

Posted Friday, November 13, 2009 5:04 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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.

Post #818402

varunragul

Posted Friday, November 13, 2009 5:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:59 AM
Points: 45, Visits: 230

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

Post #818411

steveb.

Posted Friday, November 13, 2009 5:26 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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

Post #818415

« Prev Topic | Next Topic »

Permissions