Use the following procedure to perform a full text search on a text index that uses a GENERIC term breaker.
See also: Tutorial: Performing a fuzzy full text search.
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.
Add a stoplist containing the words because, about, therefore, and only. Then, 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 MAXIMUM TERM LENGTH 30; |
Create a copy of the MarketingInformation table.
In Sybase Central, expand the Tables folder.
Right-click MarketingInformation and choose Copy.
Right-click the Tables folder and choose Paste.
In the Name field, type MarketingInformation1. Click OK.
In Interactive SQL, execute the following command to populate the new table with data:
INSERT INTO MarketingInformation1 SELECT * FROM MarketingInformation; |
On the Description column of the MarketingInformation1 table in the demo database, create a text index that references the myTxtConfig text configuration object. Set the refresh interval to 24 hours.
CREATE TEXT INDEX myTxtIndex ON MarketingInformation1 ( Description ) CONFIGURATION myTxtConfig AUTO REFRESH EVERY 24 HOURS; |
Execute the following statement to refresh the text index:
REFRESH TEXT INDEX myTxtIndex ON MarketingInformation1; |
Execute the following statements to test the text index.
This statement searches the text index for the terms cotton or cap. The results are sorted by score in descending order. Cap has a higher score than cotton because cap occurs less frequently in the text index.
SELECT ID, Description, ct.*
FROM MarketingInformation1
CONTAINS ( Description, 'cotton | cap' ) ct
ORDER BY score DESC; |
| ID | Description | Score |
|---|---|---|
| 905 | <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> |
2.2742084275032632 |
| 904 | <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> |
1.6980426550094467 |
| 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> |
0.9461597363521859 |
| 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> |
0.9244136988525732 |
| 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> |
0.9134171046194403 |
| 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> |
0.8856420222728282 |
Query 2:
SELECT ID, Description FROM MarketingInformation1 WHERE CONTAINS( Description, 'cotton -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> |
The following statements tests each row for the term cotton. If the row contains the term, a 1 appears in the Results column; otherwise, a 0 is returned.
SELECT ID, Description, IF CONTAINS ( Description, 'cotton' )
THEN 1
ELSE 0
ENDIF AS Results
FROM MarketingInformation1; |
| ID | Description | Results |
|---|---|---|
| 901 | <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 |
| 902 | <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 |
| 903 | <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 |
| 904 | <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 |
| 905 | <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 |
| 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> |
1 |
| 907 | <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 |
| 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> |
1 |
| 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> |
1 |
| 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> |
1 |
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |