Overview | Textual Datatypes | External Files | Proximity Searches | Advanced Searches | Displaying Results | Relevancy
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.
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
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 >
See also: