CODEWITHSUNDEEP
sqlsql-servert-sqlfull-text-search
Costa
Costa

Reputation: 4085

Using FTS query, Can you find all entries contains 'abc'

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

Upvotes: 3

Views: 231

Answers (3)

Tom Halladay
Tom Halladay

Reputation: 5761

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

Upvotes: 2

Tom Halladay
Tom Halladay

Reputation: 5761

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

Upvotes: 0

Icarus
Icarus

Reputation: 63966

Something like: 

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

Link to MSDN documentation

Upvotes: 2

Related Questions