Proximity searches

For example, suppose you want to search for the phrase multimedia document editor, as shown in the following example:

SELECT id, description FROM videos
    WHERE etx_contains(description,
    Row('multimedia document editor',
        'SEARCH_TYPE = WORD'));

If you execute this search on the following row, the search engine reports a match, even though the sentence is probably not of interest to you (because it has nothing to do with a multimedia document editor):

The multimedia application is now in formal beta testing. 

Alternatively, if you use an exact phrase search, the search engine fails to return rows similar to the following phrase:

The editor for multimedia documents .....

A proximity search resolves these problems by allowing you to specify the number of non-search words that can occur between two or more search words. For example, consider the following word search:

SELECT id, description FROM videos
    WHERE etx_contains(description,
    Row('multimedia document editor',
        'SEARCH_TYPE = PROX_SEARCH(8)'));

The last line uses the PROX_SEARCH setting to specify that the search engine is to return a row only when each of the three search words occurs within eight words of the last word matched. Suppose you executed this query on the following sample text:

The multimedia application is now in formal beta testing. A text editor........

In this example, the search engine does not return this row because it has more than eight words between multimedia and editor. However, the search engine does return the following row because it has fewer than eight words between each of the search words:

The editor for multimedia documents .....

The following statement always fails to produce results because the value specified with PROX_SEARCH is fewer than the number of words to search for:

SELECT id, description FROM videos
    WHERE etx_contains(description ,
    Row('multimedia document editor',
        'SEARCH_TYPE = PROX_SEARCH(2)'));