Geek asks and answers

Using the FTS query, you can find all records containing 'abc'

I'm new to full-text search, how to do a search using Contains instead of using as in the following query

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

thank

+3
source share
3 answers

Sort of:

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

MSDN Documentation Link

+2
source

Check when the last directory was populated with a 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 the full-text index to see current results. If you defined an FTS column and then loaded the data into a table, your search index is not updated.

enter image description here

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

+2
source

If "abc" is a partial match for what you are really looking for, modify the CONTAINS statement as follows:

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

OR

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

Source: MSDN - CONTAINS

0
source

More articles:


All Articles