Tutorial: Performing a fuzzy full text search

Perform a fuzzy full text search on a text index that uses an NGRAM term breaker.

Prérequis

You must have the CREATE TEXT CONFIGURATION and CREATE TABLE system privileges. You must also have the SELECT ANY TABLE system privilege or SELECT privilege on the table MarketingInformation.

Task

Start Interactive SQL. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Interactive SQL.
In the Connect window, complete the following fields:
1. In the Authentication dropdown list, select Database.
2. In the User ID field, type DBA.
3. In the Password field, type sql.
4. In the Action dropdown list, select Connect with an ODBC Data Source.

Execute the following statement to create a text configuration object called myFuzzyTextConfig. You must include the FROM clause to specify the text configuration object to use as a template.

CREATE TEXT CONFIGURATION myFuzzyTextConfig FROM default_char;

Execute the following statements to change the term breaker to NGRAM and set the maximum term length to 3. Fuzzy searches are performed using n-grams.

ALTER TEXT CONFIGURATION myFuzzyTextConfig
   TERM BREAKER NGRAM;
ALTER TEXT CONFIGURATION myFuzzyTextConfig
   MAXIMUM TERM LENGTH 3;

Start Sybase Central. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
Click Connections » Connect With SQL Anywhere 16.
In the Connect window, complete the following fields:
1. In the Authentication dropdown list, select Database.
2. In the User ID field, type DBA.
3. In the Password field, type sql.
4. In the Action dropdown list, select Connect with an ODBC Data Source.
Create a copy of the MarketingInformation table.
1. In Sybase Central, expand the Tables folder.
2. Right-click MarketingInformation and click Copy.
3. Right-click the Tables folder and click Paste.
4. In the Name field, type MarketingInformation2. Click OK.

In Interactive SQL, execute the following statement to add data to the MarketingInformation2 table:

INSERT INTO MarketingInformation2
   SELECT * FROM GROUPO.MarketingInformation;

Execute the following statement to create a text index on the MarketingInformation2.Description column that references the myFuzzyTextConfig text configuration object:

CREATE TEXT INDEX myFuzzyTextIdx ON MarketingInformation2 ( Description ) 
   CONFIGURATION myFuzzyTextConfig;

Execute the following statement to check for terms similar to coten:

SELECT MarketingInformation2.Description, ct.* 
    FROM MarketingInformation2 CONTAINS ( MarketingInformation2.Description, 'FUZZY "coten"' ) ct 
    ORDER BY ct.score DESC;

Description	Score
`<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>`	0.9461597363521859
`<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>`	0.9244136988525732
`<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>`	0.9134171046194403
`<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>`	0.8856420222728282
`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This fashionable hat is ideal for glacier travel, sea-kayaking, and hiking. With concealed draw cord for windy days.</span></p></body></html>`	0
`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A lightweight wool cap with mesh side vents for breathable comfort during aerobic activities. Moisture-absorbing headband liner.</span></p></body></html>`	0
`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>We've improved the design of this perennial favorite. A sleek and technical shirt built for the trail, track, or sidewalk. UPF rating of 50+.</span></p></body></html>`	0
`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A sporty, casual shirt made of recycled water bottles. It will serve you equally well on trails or around town. The fabric has a wicking finish to pull perspiration away from your skin.</span></p></body></html>`	0
`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This simple, sleek, and lightweight technical shirt is designed for high-intensity workouts in hot and humid weather. The recycled polyester fabric is gentle on the earth and soft against your skin.</span></p></body></html>`	0
`<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'>A polycarbonate visor with an abrasion-resistant coating on the outside. Great for jogging in the spring, summer, and early fall. The elastic headband has plenty of stretch to give you a snug yet comfortable fit every time you wear it.</span></p></body></html>`	0

Note

The last six rows have terms that contain matching n-grams. However, no scores are assigned to them because all rows in the table contain these terms.

Close Interactive SQL and Sybase Central.

Résultat

You have performed a fuzzy full text search.

Suivant

(optional) Restore the sample database (demo.db) to its original state. See Recreate the sample database (demo.db).