Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

Matches a value to one or more words using word-aware matching.
scalar-expression %CONTAINSTERM(word[,word...])
scalar-expression A scalar expression (most commonly a data column) whose values are being compared with one or more word strings.
word An alphabetic string or comma-separated list of alphabetic strings to match with values in scalar-expression. A word must be a complete word, or a multiple-word phrase (with the words separated by spaces). The word or phrase should be delimited with single quotes. See below for restrictions on multiple-word phrases.
The %CONTAINSTERM predicate allows you to select those data values that match the word or words specified in word. This comparison operation is word-aware; it is not a simple string match operation. If you specify more than one word, scalar-expression must contain all of the specified word strings. Word strings may be presented in any order. If word does not match any of the scalar expression data values, %CONTAINSTERM returns the null string.
%CONTAINSTERM is a collection predicate. It can only be used in the WHERE clause of a SELECT statement.
%CONTAINSTERM can be used on a %Text string or a character stream field.
To use %CONTAINSTERM on a string, change the %String property to %Text, and set LANGUAGECLASS and MAXLEN property parameters. For example:
Property MySentences As %Text(LANGUAGECLASS = "%Text.English",MAXLEN = 1000);
Specifying a MAXLEN value (in bytes) is required for %Text properties.
To use %CONTAINSTERM to search a character stream field, the stream field must be defined as type %Stream.GlobalCharacterSearchable. For example:
Property MyTextStream As %Stream.GlobalCharacterSearchable(LANGUAGECLASS = "%Text.English");
The %CONTAINSTERM predicate is one of the few predicates that can be used on a stream field in a WHERE clause.
The available languages are English, French, German, Italian, Japanese, Portuguese, and Spanish. See the %Text package class documentation (in %SYS) in the InterSystems Class Reference for further details.
The system generates an SQLCODE -309 error if scalar-expression is neither data type %Text nor %Stream.GlobalCharacterSearchable.
The system generates an SQLCODE -472 error if scalar-expression is not a collection-valued field (property).
The %CONTAINS and %CONTAINSTERM predicates perform the same word-aware comparison for their supplied word arguments. They differ in their requirements for multiple-word phrases:
The %CONTAINSTERM operator gives superior performance for certain types of comparisons, especially very large search sets. %CONTAINSTERM is preferable when performing comparisons in Japanese.
%CONTAINSTERM comparisons use the collation type of the scalar-expression, and are generally not case-sensitive. For this reason, the CASEINSENSITIVE class property is not applicable to %CONTAINSTERM comparisons. Collation can optionally be set to %EXACT for case-sensitive operations.
Word-Aware Matching
Caché uses language analysis rules, including punctuation analysis and stemming rules, to match only the specified word. For example, the word argument “set” would match the word “set” or “Set” (not case-sensitive), and also stem forms such as “sets” and “setting”. However, it would not match words such as “setscrew,” “settle,” or “Seth,” even though these words contain the specified string.
Stemming rules provide for matching between any two forms of the word stem. Stemming is performed on both the search term(s) and the searched text. For example, you can specify %CONTAINSTERM('jumping') and match the word “jumps” in the text.
This word-aware matching is fundamentally different from the character-by-character string matching performed by the SQL Contains operator ([).
For further details on contains comparison, refer to the %CONTAINS operator.
Collection Predicates
%CONTAINSTERM is a collection predicate. It can be used in most contexts where a predicate condition can be specified, as described in the Overview of Predicates page of this manual. It is subject to the following restrictions:
iKnow and iFind
The Caché iKnow text analysis tool and iFind text search tool also provide word-aware analysis. These facilities are entirely separate from %Text classes. They provide a substantially different and significantly more sophisticated form of textual analysis.
The following Embedded SQL example performs a %CONTAINSTERM comparison with a literal phrase:
  &sql(SELECT name,stats
       INTO :badname, :badstat
       FROM Sample.Employee
       WHERE status %CONTAINSTERM('invalid value'))
The following Embedded SQL example uses a host variable to perform a %CONTAINSTERM comparison:
  SET text="invalid"
  &sql(SELECT name,stats
       INTO :badname, :badstat
       FROM Sample.Employee
       WHERE status %CONTAINSTERM(:text))
See Also