Phrase searching with pattern matching

When you perform a search with SEARCH_TYPE = PHRASE_EXACT, the search text must contain a phrase that is identical to the clue for a hit to occur. If you specify a pattern search in addition to an exact phrase search (by specifying PATTERN_ALL, for example), the individual words in the clue must pattern-match the corresponding words in the search text in the same order in which the words appear in the search text.

For example, in a pattern search combined with an exact phrase search, the text jill john jones matches the clue jyll jonh gones but does not match the clue john jill jones. That is, order always counts in an exact phrase search, regardless of whether you also specify pattern matching.

In an exact phrase search, all words in the clue (or pattern matches thereof) must be found in the search text. Partial matches, where one or more words are missing, do not count as hits.

When you perform a search with SEARCH_TYPE = PHRASE_APPROX, the search text must contain either a phrase identical to the clue, one or more words of the clue in the same order, or one or more words of the clue in a different order. The module uses the number of words and word order to produce a document score for all hits; the more words in closer order a search text has, the higher the score it is assigned.

For example, if the clue is “drop many balls,” the search engine produces the following ranking based on scores: 
He can drop many balls                (exact match)
He has many balls                     (2 words, in same order as clue) 
He let five balls drop                (2 words, different order from clue) 
He has many children                  (1 word)
A keyword pattern search works like a search with SEARCH_TYPE = PHRASE_APPROX. However, a pattern search adds pattern matching on a word-by-word basis. For example, if the clue is “drop many balls,” then the search engine produces the following ranking: 
He can dorp many valls                (assuming dorp pattern matches drop,
                                       and so on)
He has mani balds
He let five galls frop
He has many children
Tip: Refrain from setting SEARCH_TYPE to PHRASE_APPROX and passing the PATTERN_BASIC or PATTERN_ALL tuning parameters for the same search. Doing so can result in poor performance for searches of this type.
The tuning parameters that instruct the text search engine that you want transpositions and substitutions taken into account are PATTERN_TRANS and PATTERN_SUBS. Unlike the SEARCH_TYPE tuning parameter, PATTERN_TRANS and PATTERN_SUBS do not have values that you set. Instead, you pass these tuning parameters directly to the search engine by using the Boolean operator &, as shown in the following example: 
SELECT * FROM videos
    WHERE etx_contains(description,
    Row('multimedia' , 'PATTERN_TRANS & PATTERN_SUBS')) ;
This example initiates a fuzzy search for the multimedia clue. The search engine returns all words that have exactly one substitution or one transposition, as well as words that match the clue exactly. The following figure displays the resulting hitlist when the query in the example is run on the videos table.
Figure 1: Sample text search query that uses tuning parameters

Shows the use of combining the PATTERN_TRANS and PATTERN_SUBS tuning parameters for the keyword "multimedia." The hitlist from the videos table is the rows that contain the words: "multimedia," "multymedia," and "mulitmedia."

The tuning parameters PATTERN_TRANS and PATTERN_SUBS only take into account the transposition or substitution of a single letter per word.