Pattern search

A pattern search is a fuzzy search that takes into account multiple transpositions and substitutions and also returns any substrings or superstrings of the clue.

A superstring pattern match is when the clue is found within a larger word, such as when a search for the clue edit returns editable.

A substring pattern match is when part of the clue is found within a smaller word, such as when a search for the clue irretrievable returns retrievable.

To enable a pattern search, you pass the PATTERN_ALL tuning parameter to the search engine.

For example, to perform a pattern search for the word retrieve, execute the following SQL statement:

SELECT id, description FROM videos
    WHERE etx_contains(description,
    Row ('retrieve','PATTERN_ALL'));

Given the search text shown in the following example, the search engine returns rows that contain retrieved, retrieval, or retreive. Because of the high default word score threshold of 70, the word irretrievable is not found. However, if you use the WORD_SCORE tuning parameter to set the word score lower, the word irretrievable might be returned.

Shows the use of the PATTERN_ALL tuning parameter for the pattern of text "retrieve." The hitlist from the videos table is the rows that contain the words: "retrieval," "retrieved," and "retreive." — Figure 1: Example of pattern search

A pattern search differs from a substitution or a transposition search in that a pattern search allows multiple substitutions and transpositions. A substitution search returns words that differ from the clue by a single substitution. For example, a search for the word travel with PATTERNS_SUBS set might return gravel. A transposition search returns words that contain a single transposition. For example, a search for the word travel with PATTERN_TRANS set might return travle.

In contrast, a search for the same clue with PATTERN_ALL enabled returns words such as traveled, travelled, unraveled, and travvel, in addition to gravel and travle. The text search engine assigns a higher word score to words matched with PATTERNS_SUBS or PATTERN_TRANS enabled than it does to words that are matched with PATTERN_ALL enabled.

If you want to enable basic pattern matching, use the PATTERN_BASIC tuning parameter. The search might return transpositions, substitutions, and superstring and substring pattern matches, depending on the value of WORD_SCORE.

The PATTERN_ALL tuning parameter is equivalent to specifying the three parameters PATTERN_BASIC, PATTERN_SUBS, and PATTERN_TRANS at once.