Expand the Tables folder.

Right-click MarketingInformation and choose Copy.

Right-click the Tables folder and choose Paste.

In the Name field, type MarketingInformationNgram.

Click OK.

The following statement searches the 2-GRAM text index for terms containing sw. The results are sorted by score in descending order.

SELECT M.Description, ct.*
   FROM MarketingInformationNgram AS M 
CONTAINS( M.Description, 'sw' ) ct
   ORDER BY ct.score DESC;

The following statement searches for terms containing ams. The results are sorted by score in descending order.

SELECT M.Description, ct.*
   FROM MarketingInformationNgram AS M 
CONTAINS( M.Description, 'ams' ) ct
   ORDER BY ct.score DESC;

With the 2-GRAM text index, the previous statement is semantically equivalent to:

SELECT M.Description, ct.*
   FROM MarketingInformationNgram AS M 
CONTAINS( M.Description, '"am ms"' ) ct
   ORDER BY ct.score DESC;

The following statement searches for terms with v followed by any alphanumeric character. Because ve occurs more frequently in the indexed data, rows that contain the 2-gram ve are assigned a lower score than rows containing vi. The results are sorted by score in descending order.

SELECT M.ID, M.Description, ct.*
   FROM MarketingInformationNgram AS M 
CONTAINS( M.Description, 'v*' ) ct
   ORDER BY ct.score DESC;

The following statements search each row for any terms containing v. After the second statement, the variable contains the string av OR ev OR iv OR ov OR rv OR ve OR vi OR vo. The results are sorted by score in descending order. When an n-gram appears in all indexed rows, it is assigned a score of zero.

This is the only method that allows a single character to be located if it appears before a whitespace or a non-alphanumeric character.

CREATE VARIABLE query NVARCHAR (100);
SELECT LIST (term, ' OR ' )
INTO query
   FROM sa_text_index_vocab( 'ncharNGRAMTextIndex', 'MarketingInformationNgram', 'dba' )
   WHERE term LIKE '%v%';
SELECT M.ID, M.Description, ct.*
   FROM MarketingInformationNgram AS M 
   CONTAINS( M.Description, query ) ct
   ORDER BY ct.score DESC;

The following statement searches the Description column for rows that contain ea, ka, and ki.

SELECT M.ID, M.Description, ct.*
   FROM MarketingInformationNgram AS M 
CONTAINS( M.Description, 'ea ka ki' ) ct
  ORDER BY ct.score DESC;

The following statement searches the Description column for rows that contain ve and vi, but not gg.

SELECT M.ID, M.Description, ct.*
   FROM MarketingInformationNgram AS M 
CONTAINS( M.Description, 've & vi -gg' ) ct
   ORDER BY ct.score DESC;