End-to-end example of full text searching statements

The following procedure allows you to test the statements that support the full text searching feature. The examples are simple and are designed for you to use with the demo database.

To test the full text searching statements

Create the text configuration object.

The following example creates a text configuration object called myTxtConfig. Remember that you must include the FROM clause to specify the text configuration object to use as a template.
CREATE TEXT CONFIGURATION myTxtConfig FROM default_char;

Customize the text configuration object.

Once you create the text configuration object, you want to add a stoplist (terms to ignore when indexing) and set the maximum term length to 30. You must do this in separate ALTER TEXT CONFIGURATION statements, as follows:

ALTER TEXT CONFIGURATION myTxtConfig
   STOPLIST 'because about therefore only';
ALTER TEXT CONFIGURATION myTxtConfig
   MINIMUM TERM LENGTH 4;
ALTER TEXT CONFIGURATION myTxtConfig
   MAXIMUM TERM LENGTH 30;

Note

The example statements above that alter the term lengths are for demonstration purposes only. Changing the default term length settings should only be done if the data in the columns used in the index justify it. That is, change the defaults only when they exclude terms that you need to search on, or when they include terms that you never want to search on.

Create the text index.

Now you want to create a text index that references the myTxtConfig text configuration object. For the purposes of this example, you can build it on the Description column of the MarketingInformation table in the demo database. The data in the table is not expected to change often, so you decide to set up a refresh interval of 24 hours.

CREATE TEXT INDEX myTxtIndex ON MarketingInformation ( Description ) 
   CONFIGURATION myTxtConfig
   AUTO REFRESH EVERY 24 HOURS;

Refresh the text index.

The following statement refreshes the text index you created:
REFRESH TEXT INDEX myTxtIndex ON MarketingInformation;

Test the text index.

Query 1:

SELECT ID, Description 
   FROM MarketingInformation 
   WHERE CONTAINS ( Description, 'cotton | cap' );

ID	Description
906	<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton construction. Shields against sun and precipitation. Metallic ions in the fibers inhibit bacterial growth, and help neutralize odor.</span></p></body></html>
908	<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage. </span></p></body></html>
909	<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% cotton/20% polyester blend makes it easy to keep them clean.</span></p></body></html>
910	<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying cotton shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html>

Note that the search is a fuzzy search so the term cap returns a row with the word cap in the Description column.

Query 2:

SELECT ID, Description 
   FROM MarketingInformation 
   WHERE CONTAINS( Description, 'cotton' ) 
      AND NOT CONTAINS( Description, 'visor' );

ID	Description
908	<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown cotton hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage.</span></p></body></html>
909	<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% cotton/20% polyester blend makes it easy to keep them clean.</span></p></body></html>
910	<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying cotton shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html>

Query 3:

SELECT IF CONTAINS ( Description, 'cotton' )
      THEN 1
      ELSE 0
      ENDIF AS Results
   FROM MarketingInformation;

Results
0
0
0
0
0
1
0
1
1
1

Send feedback about this page via email or DocCommentXchange