Here is an example of how it works, using a table that contains all 6.5 million entries downloaded from the English
version of Wikipedia:
CREATE TABLE enwiki_entry ( -- 6,552,490 rows, 17.2G total
page_number BIGINT NOT NULL,
from_line_number BIGINT NOT NULL,
to_line_number BIGINT NOT NULL,
page_title VARCHAR ( 1000 ) NOT NULL,
page_id VARCHAR ( 100 ) NOT NULL,
page_text LONG VARCHAR NOT NULL,
PRIMARY KEY CLUSTERED ( page_number ) );

The first step is to define a text index on the columns to be searched:

CREATE TEXT INDEX tx_page_text
ON enwiki_entry ( page_text )
MANUAL REFRESH;

The second step is to build the text index, a process that can take quite a long time if the table is very large:
REFRESH TEXT INDEX tx_page_text
ON enwiki_entry
WITH EXCLUSIVE MODE
FORCE BUILD;

Once the index is built, however, queries that use the index are very fast. The following SELECT uses the new CONTAINS clause to find all the Wikipedia entries containing the exact phrase “Ayn Rand":

SELECT score, 
enwiki_entry.page_title,
LEFT ( enwiki_entry.page_text, 500 ) AS excerpt
FROM enwiki_entry
CONTAINS ( enwiki_entry.page_text,
'"Ayn Rand"' )
ORDER BY score DESC;

The CONTAINS clause applies the query string ‘“Ayn Rand”’ to the enwiki_entry.page_text column using the full text index previously defined on that column, thus limiting the result set to rows that match. The CONTAINS clause also returns an implicit “score” column which measures how closely each row matches the query string. The ORDER BY clause uses that column to sort the best matches to the top, and Figure 6 shows the results in a browser display produced by a SQL Anywhere 11 web service.
Here’s a tip: Don’t do like I did and leave out an important column from the CREATE TEXT INDEX statement. In Figure 6 the main Wikipedia entry entitled “Ayn Rand” doesn’t appear on the first page, but it should, and it would have if I hadn’t forgotten to include the enwiki_entry.page_title column in the index. When a full text index specifies multiple columns the CONTAINS clause calculates a score that counts both columns, and in this case the row with “Ayn Rand” in the title would
have received a very high score for the query string ‘“Ayn Rand”’.
There are many, many options available with full text searching, I’ve only touched on the basics. Here’s one of the extras:
If you build an index on two columns, you can refer to one column in the CONTAINS clause and the search will only look at that column... another reason not to make the mistake I did, better to index on more columns, not fewer, you’ll have more freedom when designing your queries.
Oh, and by the way: The default boolean operator is AND, just like in Google. In other words, the query strings ‘Ayn Rand’ and ‘Ayn AND Rand’ are the same, and they’re different from ‘Ayn OR Rand’.