<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="18. Free Text Search" />
<meta name="dc.subject" content="18. Free Text Search" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="freetext.html" title="Chapter Contents" />
<link rel="prev" href="creatingtxtidxs.html" title="Creating Free Text Indexes" />
<link rel="next" href="txttrig.html" title="Text Triggers" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>18. Free Text Search</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="queryingftcols" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>18. Free Text Search</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="freetext.html">Chapter Contents</a> | <a class="link" href="creatingtxtidxs.html" title="Creating Free Text Indexes">Prev</a> | <a class="link" href="txttrig.html" title="Text Triggers">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="freetext.html">Free Text Search</a>
</div>
<br />
<div>
<a href="txtidxquickstart.html">Basic Concepts</a>
</div>
<div>
<a href="creatingtxtidxs.html">Creating Free Text Indexes</a>
</div>
<div class="selected">
<a href="queryingftcols.html">Querying Free Text Indexes</a>
<div>
<a href="#containspredicate" title="CONTAINS predicate">CONTAINS predicate</a>
<a href="#fttcomments" title="Comments">Comments</a>
<a href="#textexprsyntax" title="Text Expression Syntax">Text Expression Syntax</a>
</div>
</div>
<div>
<a href="txttrig.html">Text Triggers</a>
</div>
<div>
<a href="tablesandinternals.html">Generated Tables and Internals</a>
</div>
<div>
<a href="droptxtindex.html">Removing A Text Index</a>
</div>
<div>
<a href="droptxttrig.html">Removing A Text Trigger</a>
</div>
<div>
<a href="ftinternationalization.html">Internationalization & Unicode</a>
</div>
<div>
<a href="ftperformance.html">Performance</a>
</div>
<div>
<a href="fttfuncs.html">Free Text Functions</a>
</div>
<br />
</div>
<div id="text">
<a name="queryingftcols" />
<h2>18.3. Querying Free Text Indexes</h2>
<a name="containspredicate" />
<h3>18.3.1. CONTAINS predicate</h3>
<p>
Returns TRUE if a free text indexed column matches a text expression.
</p>
<p>
Syntax
</p>
<div>
<pre class="programlisting">
contains_pred:
contains (column, expr, opt_or_value ....)
opt_or_value:
DESCENDING
| START_ID ',' scalar_exp
| END_ID ',' scalar_exp
| SCORE_LIMIT ',' scalar_exp
| RANGES ',' variable
| OFFBAND column
variable: IDENTIFIER
</pre>
</div>
<p>
The <strong>column</strong> must refer to a column for which there exists a free text index.
The <strong>expr</strong> must be a narrow or wide string expression whose syntax matches the
rules in 'Text Query Syntax'.
The <strong>START_ID</strong> is the first allowed document ID to be selected by the
expression in its traversal order, e.g. least or equal for ascending and
greatest or equal for descending.
<strong>END_ID</strong> is the last allowed ID in the traversal order. For descending order
the START_ID must be >= END_ID for hits to be able to exist. For ascending
order the START_ID must be <= END_ID for hits to be able to exist.
</p>
<p>
<strong>DESCENDING</strong> specifies that the search will produce the hit with the greatest
ID first, as defined by integer or composite collation.
RANGES specifies that the query variable following the RANGES keyword will
be bound to the word position ranges of the hits of the expression inside
the document. The variable is in scope inside the enclosing SELECT
statement.
</p>
<p>
<strong>SCORE_LIMIT</strong> specifies a minimum score that hits must have or exceed to be
considered matches of the predicate.
</p>
<p>
<strong>OFFBAND</strong> specifies that the following column will be retrieved from the free
text index instead of the actual table. For this to be possible the column
must have been declared as offband with the CLUSTERED WITH option of the
<a href="creatingtxtidxs.html#createtxtidxstmt">CREATE TEXT INDEX</a> statement.
</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="fn_contains.html">contains()</a>
</p>
<p>The <a href="queryingxmldata.html#xcontainspredicate">XCONTAINS Predicate</a>.</p>
</div>
<br />
<a name="fttcomments" />
<h3>18.3.2. Comments</h3>
<p>
<strong>Order</strong> - If the select statement containing the contains predicate does not
specify an exact match of the primary key of the table having the contains
predicate, then the contains predicate will be the 'driving' condition,
meaning that rows come in ascending or descending order of the free text
document ID.
</p>
<p>
The <strong>DESCENDING</strong> keyword specifies the descending order of the free text
index document ID and has nothing to do with a possible ORDER BY of the
enclosing statement. Even if there is an ORDER BY in the enclosing
statement the DESCENDING keyword of contains has an effect in the
interpretation of the STRT_ID and END_ID contains options.
</p>
<p>
If there is a full equality match of the primary key of the table, this will
be the driving predicate and contains will only be used to check if the text
expression matches the single row identified by the full match of the
primary key.
</p>
<p>
The contains predicate may not appear outside of a select statement and may
only reference a column for which a free text index has been declared. The
first argument must be a column for which there is such an index. The text
expression may be variable and computed, although
it must be constant during the evaluation of the select statement containing
it.
</p>
<p>
The contains predicate must be a part of the top level AND of the WHERE
clause of the containing select. It may not for example be a term of an OR
predicate in the select but can be AND'ed with an OR expression.
</p>
<br />
<a name="textexprsyntax" />
<h3>18.3.3. Text Expression Syntax</h3>
<div>
<pre class="programlisting">
expr ::= proximity_expr
expr AND expr
| expr OR expr
| expr AND NOT expr
| '(' expr ')'
word_expr ::=
word
| '"' phrase '"'
proximity_expr ::=
word_expr
| proximity_expr NEAR word_expr
word ::=
<word char>*
phrase ::=
word
| phrase <whitespace> word
word_char ::= alphanumeric characters, '*', ISO Latin accented characters.
</pre>
</div>
<p>
A word is a sequence of word characters. A phrase is a sequence of words
separated by white spaces and enclosed in double quotes. If a word contains a wildcard
character it must be quoted with double quotes.
</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>
An expression may not consist of all negative terms, e.g. (not a) and (not
b) is not a valid expression but 'c and not a and not b' is a valid
expression.
</p>
<p>
Note that the NEAR connective may not be used between AND'ed or
OR'ed terms. It can be used to combine words or phrases.
</p>
</div>
<a name="ex_qryfti" />
<div class="example">
<div class="exampletitle">Querying Free Text Indexed Columns</div>
<div>
<pre class="programlisting">
select count (*) from docs
where contains (text, '"virtual database"')
</pre>
</div>
<p>returns the count of documents with one or more occurrences of
"virtual" immediately
followed by "database".</p>
<div>
<pre class="programlisting">
'performance and (tuning or optimization)'
</pre>
</div>
<p>
specifies documents
with performance and either 'tuning' or optimization' in any
respective positions.
</p>
<div>
<pre class="programlisting">
'graphics and not (graphics near user near interface)'
</pre>
</div>
<p>
matches documents with the word graphics more than 100
words away from 'user' or 'interface'.
</p>
<div>
<pre class="programlisting">
'"sql interfac*"'
</pre>
</div>
<p>
matches documents with SQL followed by a word beginning with 'interfac'.
</p>
<div>
<pre class="programlisting">
'"dragon*" and not "once upon a time"'
</pre>
</div>
<p>
matches documents with words beginning with 'dragon' and not containing the phrase
'once upon a time'.
</p>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="creatingtxtidxs.html" title="Creating Free Text Indexes">Previous</a>
<br />Creating Free Text Indexes</td>
<td align="center" width="34%">
<a href="freetext.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="txttrig.html" title="Text Triggers">Next</a>
<br />Text Triggers</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
840
