Tutorial: Performing a fuzzy full text search

Use the following procedure to perform a fuzzy full text search on a text index that uses a NGRAM term breaker.

Perform a fuzzy full text search on a NGRAM term index

Execute the following statement to create a text configuration object called myFuzzyTextConfig.
CREATE TEXT CONFIGURATION myFuzzyTextConfig FROM default_char;

Execute the following statement to change the term breaker to NGRAM and set the maximum term length to 3. Fuzzy searches are performed using n-grams. Separate ALTER TEXT CONFIGURATION statements are used to implement these changes:

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

Create a copy of the MarketingInformation table.
1. In Sybase Central, expand the Tables folder.
2. Right-click MarketingInformation and choose Copy.
3. Right-click the Tables folder and choose Paste.
4. In the Name field, type MarketingInformation2. Click OK.
Execute the following command to add data to the MarketingInformation2 table:
INSERT INTO MarketingInformation2 SELECT * FROM MarketingInformation;

Execute the following command 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 statements to test the text index.

The following fuzzy query checks 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 row in the table contain these terms.