3

I am new to Full Text Search, how do I perform a search using Contains instead of using like in the following query

Select * From Students Where FullName LIKE '%abc%'

Thanks

Costa
  • 3,897
  • 13
  • 48
  • 81

3 Answers3

2

Something like: 

SELECT * From Students Where CONTAINS(FullName,'abc')

Link to MSDN documentation

Icarus
  • 63,293
  • 14
  • 100
  • 115
  • I think this is not correct, I inserted three records like this 'abc bbb 123' and I tried that query. it returns 03 rows. Because FTS works on word boundry. However LIKE query returns 1100 record!! – Costa May 08 '12 at 16:51
  • That may not be a problem with the query itself, but the Catalog indexing options. 1) Have you set up a FTS Catalog Schedule and 2) When was the last time it ran? – Tom Halladay May 08 '12 at 18:31
  • The index is populated, I think no problem!! but how you would verify that?? – Costa May 08 '12 at 18:39
  • See the image in my answer. I'm not sure if you can see a 'last updated' value unless you have a schedule set up. But you can just re-populate it, and test it again. – Tom Halladay May 08 '12 at 18:46
2

Check when your catalog was last populated using this script:

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'FTS_Demo_Catalog'

SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs AS cat

You may need to re-populate your Full Text Index in order to see current results. If you defined the FTS column, and then loaded data into the table, your search index is not up to date.

enter image description here

If you need this to be regularly updated, check out this article on Tech Net

Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
0

If "abc" is a partial match for what you are really seeking, alter your CONTAINS statement like this:

SELECT   * 
FROM     Students 
WHERE    CONTAINS(FullName, '"abc*"')

OR

SELECT   * 
FROM     Students 
WHERE    CONTAINS(FullName, '"*abc*"')

Source: MSDN - CONTAINS

Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
  • I don't know what "Anta betefti ya" means. But I realized there was a typo in my code, I wrote "FORM" instead of "FROM". Try it again? – Tom Halladay May 08 '12 at 19:25
  • Also, from your related post "Why I would bother using full text search?" @ http://stackoverflow.com/questions/10502872/why-i-would-bother-using-full-text-search, You mention having millions of records. The indexing process on millions of rows could potentially take hours. You need to step back a minute and make sure that 1) Your catalog has been populated AFTER the rows have been inserted, and 2) if you just started your catalog population, that it has actually finished. – Tom Halladay May 08 '12 at 19:30