You can specify multiple terms separated by Boolean operators when performing full text searches. SQL Anywhere supports the following Boolean operators when performing a full text search: AND, OR, and AND NOT.
For more information about the syntax for boolean searching, see CONTAINS search condition.
The AND operator matches a row if it contains both of the terms specified on either side of the AND. You can also use an ampersand (&) for the AND operator. If terms are specified without an operator between them, AND is implied.
The order in which the terms are listed is not important.
For example, each of the following statements finds rows in MarketingInformation.Description that contain the term fabric and a term that begins with ski:
SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'ski* AND fabric' ); SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'fabric & ski*' ); SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'ski* fabric' ); |
The OR operator matches a row if it contains at least one of the specified search terms on either side of the OR. You can also use a vertical bar (|) for the OR operator; the two are equivalent.
The order in which the terms are listed is not important.
For example, either statement below returns rows in the MarketingInformation.Description that contain either the term fabric or a term that starts with ski:
SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'ski* OR fabric' ); SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'fabric | ski*' ); |
The AND NOT operator finds results that match the left argument and do not match the right argument. You can also use a hyphen (-) for the AND NOT operator; the two are equivalent.
For example, the following statements are equivalent and return rows that contain the term fabric, but do not contain any terms that begin with ski.
SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'fabric AND NOT ski*' ); SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'fabric -ski*' ); SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'fabric & -ski*' ); |
The boolean operators can be combined in a query string. For example, the following statements are equivalent and search the MarketingInformation.Description column for items that contain fabric and skin, but not cotton:
SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'skin fabric -cotton' ); SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'fabric -cotton AND skin' ); |
The following statements are equivalent and search the MarketingInformation.Description column for items that contain fabric or both cotton and skin:
SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'fabric | cotton AND skin' ); SELECT * FROM MarketingInformation WHERE CONTAINS ( MarketingInformation.Description, 'cotton skin OR fabric' ); |
Terms and expressions can be grouped with parentheses. For example, the following statement searches the MarketingInformation.Description column for items that contain cotton or fabric, and that have terms that start with ski.
SELECT ID, Description FROM MarketingInformation WHERE CONTAINS( MarketingInformation.Description, '( cotton OR fabric ) AND shi*' ); |
Description | |
---|---|
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> |
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> |
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> |
You can perform a full text search across multiple columns in a single query, as long as the columns are part of the same text index.
SELECT * FROM Products WHERE CONTAINS ( t.c1, t.c2, 'term1|term2' ); |
SELECT * FROM t WHERE CONTAINS( t.c1, 'term1' ) OR CONTAINS( t.c2, 'term2' ); |
The first query matches if t1.c1 contains term1, or if t1.c2 contains term2.
The second query matches if either t1.c1 or t1.c2 contains either term1 or term2. Using the contains in this manner also returns scores for the matches. See Scoring full text search results.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |