Administration: Omnidex Indexing

Omnidex Text

Advanced Searches

When administrators speak of a complex query, they are usually referring to the complex Boolean and parenthetical relationships between pieces of criteria. By its very nature, an individual SQL criterion is not complex. Even if there are 20 pieces of criteria, each criteria is something like a name, an address or a product code.

Textual searches can involved complex criteria within an individual column. It may be necessary to have complex Boolean relationships between words in a large article. It may also be necessary to apply text features, such as misspellings, synonyms, word forms, etc. To support these advanced searches, Omnidex provides the $CONTAINS function.

$CONTAINS Function

The $CONTAINS function allows more control over an individual SQL predicate against a column. In its simplest form, the following two statements are identical:

> select title from books where title = 'Hamlet';

TITLE
-----------------------------------------------------------------------------
Hamlet
1 row returned
>
> select title from books where $contains(title, 'Hamlet');

TITLE
-----------------------------------------------------------------------------
Hamlet
1 rows returned
>

The $CONTAINS function also allows options that manually engage the PowerSearch features, such as misspellings, synonyms, etc.

> select title from books where $contains(content, 'missisipi','misspellings');

TITLE
-----------------------------------------------------------------------------
Around the World in Eighty Days
The Adventures of Tom Sawyer
2 rows returned

The $CONTAINS function allows criteria to be labelled for eventual pairing with excerpts using the $CONTEXT function or relevancy scoring using the $SCORE function:

> select        TITLE
>>  from        BOOKS
>>  where       $contains(LANGUAGE, 'English',, 'LANGUAGE') and
>>              $contains(CONTENT, 'missisipi', 'misspellings', 'CONTENT');

TITLE
-----------------------------------------------------------------------------
Around the World in Eighty Days
The Adventures of Tom Sawyer
2 rows returned

$CONTEXT Function

The $CONTEXT function retrieves excerpts of a text field based on a paired $CONTAINS function. By default, a simple excerpt is displayed; however, options exist to allow embedding HTML tags to highlight the search terms for easy display in a web environment.

> select        TITLE,
>>              $context
>>  from        BOOKS
>>  where       $contains(CONTENT, 'missisipi', 'misspellings');
TITLE
-----------------------------------------------------------------------------
$CONTEXT(BOOKS.CONTENT)
-----------------------------------------------------------------------------
Around the World in Eighty Days
--- at Nauvoo, on the *Mississippi*, numbering twenty-five thousand ---
>> night it crossed the *Mississippi* at Davenport, and by ---

The Adventures of Tom Sawyer
--- a point where the *Mississippi* River was a trifle --- and saw the broad
>> *Mississippi* rolling by! ---
2 rows returned

Excerpts can be easily formatted for display using HTML, including assigning CSS classes as needed:

> select        TITLE,
>>              $context(255, 'STYLE=HTML CLASSES')
>>  from        BOOKS
>>  where       $contains(CONTENT, 'missisipi', 'misspellings');
TITLE
-----------------------------------------------------------------------------
$CONTEXT(BOOKS.CONTENT)
-----------------------------------------------------------------------------
Around the World in Eighty Days
--- at Nauvoo, on the <span class="odx_word">Mississippi</span>, numbering
>> twenty-five thousand --- night it crossed the <span
>> class="odx_word">Mississippi</span> at Davenport, and by ---
The Adventures of Tom Sawyer
--- a point where the <span class="odx_word">Mississippi</span> River was a
>> trifle --- and saw the broad <span class="odx_word">Mississippi</span>
>> rolling by! ---
2 rows returned

If the statement contains multiple $CONTAINS functions, they should be labelled with distinct names, and the $CONTEXT should reference the appropriate $CONTAINS clause. The excerpts will be created based on that column's criteria.

select        TITLE,
              $CONTEXT(255, 'STYLE=TEXT', 'CONTENT')
  from        BOOKS
  where       $contains(LANGUAGE, 'English',, 'LANGUAGE') and
              $contains(CONTENT, 'missisipi', 'misspellings', 'CONTENT');

TITLE
-----------------------------------------------------------------------------
$CONTEXT(BOOKS.CONTENT)
-----------------------------------------------------------------------------
Around the World in Eighty Days
--- at Nauvoo, on the *Mississippi*, numbering twenty-five thousand ---
>> night it crossed the *Mississippi* at Davenport, and by ---
The Adventures of Tom Sawyer
--- a point where the *Mississippi* River was a trifle --- and saw the broad
>> *Mississippi* rolling by! ---
2 rows returned
>

Additional Resources

See also:

 
Back to top
admin/indexing/text/advanced.txt ยท Last modified: 2016/06/28 22:38 (external edit)