Examples of SQL Server Full Text Index Queries

Examples of using SQL Server Full text query

1)       CONTAINS - Is a predicate used in a WHERE clause to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches.

--The following example finds all records with word "hypersonic" in <column_name> column

SELECT <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, 'hypersonic');

GO

--The following example finds all records with word "hypersonic" or "liquid" in <column_name> column

SELECT <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, ' "hypersonic" OR "liquid" ')

GO

--The following example finds all records with word "hypersonic" AND "liquid" in <column_name> column

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, ' "hypersonic" AND "liquid" ')

GO

--The following example finds all records with at least one word starting with the prefix chain word "hyper"

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, ' "hyper*" ')

GO

--The following example finds all records with word starting with hyper OR word starting with heal

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, ' "hyper*" OR "heal*" ')

GO

--The following example finds all records with word starting with hyper and word starting with heal

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, ' "hyper*" AND "heal*" ')

GO

--The following example returns all records that have the word australian near the word health.

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, ' australian NEAR health ')

GO

--The following example searches for all records with words of the form ride: riding, ridden, and so on.

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, ' FORMSOF (INFLECTIONAL, ride) ');

GO

--The following example searches for records containing the words performance, comfortable, or smooth, and different weightings are given to each word.

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE CONTAINS(<column_name>, 'ISABOUT (performance weight (.8),

comfortable weight (.4), smooth weight (.2) )' );

GO

2)       FREETEXT - Is a predicate used in a WHERE clause to search columns containing character-based data types for values that match the meaning and not just the exact wording of the words in the search condition.

-- The following example searches for all records in column <column_name> containing the words related to "australian", "health", and "safety".

SELECT id, <column_name>

FROM dbo.<table_name>

WHERE FREETEXT(<column_name>, 'australian health safety' );

GO

3)       CONTAINSTABLE - Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can only be referenced in the FROM clause of a SELECT statement as if it were a regular table name. Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

-- The following example searches for all records in column <column_name> containing the words nitride and also gives rank 

SELECT  FT_TBL.id

    ,FT_TBL.<column_name>

    ,KEY_TBL.RANK as score

FROM dbo.<table_name>  AS FT_TBL

    INNER JOIN CONTAINSTABLE(dbo.<table_name>, <column_name>,

        'nitride') AS KEY_TBL

        ON FT_TBL.id = KEY_TBL.[KEY]

ORDER BY RANK desc

SELECT  FT_TBL.id

    ,FT_TBL.<column_name>

    ,KEY_TBL.RANK as score

FROM dbo.<table_name>  AS FT_TBL

    INNER JOIN CONTAINSTABLE(dbo.<table_name>, <column_name>,

        'ISABOUT (performance weight (.8),

comfortable weight (.4), smooth weight (.2) )') AS KEY_TBL

        ON FT_TBL.id = KEY_TBL.[KEY]

ORDER BY RANK desc

4)       FREETEXTTABLE - Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can only be referenced in the FROM clause of a SELECT statement like a regular table name. Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row.

-- The following example searches for all records in column <column_name> containing the words nitride and also gives rank 

SELECT  FT_TBL.id

    ,FT_TBL.<column_name>

    ,KEY_TBL.RANK as score

FROM dbo.<table_name>  AS FT_TBL

    INNER JOIN FREETEXTTABLE(dbo.<table_name>, <column_name>,

        'nitrides of boron') AS KEY_TBL

        ON FT_TBL.id = KEY_TBL.[KEY]

ORDER BY RANK desc