Use the following procedure to perform a full text search on a text index that uses a GENERIC term breaker.
Start Interactive SQL and connect to the sample database using the SQL Anywhere 12 Demo data source.
Create the text configuration object.
The following example creates a text configuration object called myTxtConfig. 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; |
Start Sybase Central and connect to the sample database using the SQL Anywhere 12 Demo data source.
Create a copy of the MarketingInformation table.
Expand the Tables folder.
Right-click MarketingInformation and click Copy.
Right-click the Tables folder and click Paste.
In the Name field, type MarketingInformation1.
Click OK.
In Interactive SQL, execute the following statement to populate the new table with data:
INSERT INTO MarketingInformation1 SELECT * FROM MarketingInformation; |
On the Description column of the MarketingInformation1 table in the sample 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 |
The following statement searches the text index for the term cotton. Rows that also contain the word visor are discarded. The results are not scored because the clause CONTAINS uses a predicate.
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 statement 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 |
Disconnect from Interactive SQL and Sybase Central.
(optional) Restore the sample database (demo.db) to its original state.
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |