Caché SQL Reference
Matches a value to one or more words using word-aware matching.
||A scalar expression (most commonly a data column) whose values are being compared with one or more word strings.
||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.
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
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.
can be used on a %Text string or a character stream field.
Property MySentences As %Text(LANGUAGECLASS = "%Text.English",MAXLEN = 1000);
Specifying a MAXLEN value (in bytes) is required for %Text
Property MyTextStream As %Stream.GlobalCharacterSearchable(LANGUAGECLASS = "%Text.English");
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.
Caché generates an SQLCODE -309 error if scalar-expression
is neither data type %Text nor %Stream.GlobalCharacterSearchable.
Caché generates an SQLCODE -472 error if scalar-expression
is not a collection-valued field (property).
%CONTAINS and %CONTAINSTERM
predicates perform the same word-aware comparison for their supplied word
arguments. They differ in their requirements for multiple-word phrases:
argument phrase must be NGRAMLEN words or less in length. A %CONTAINS
argument phrase can be any number of words in length.
operator gives superior performance for certain types of comparisons, especially very large search sets. %CONTAINSTERM
is preferable when performing comparisons in Japanese.
comparisons use the collation 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.
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
You cannot use %CONTAINSTERM
as a predicate that selects fields for a JOIN
You cannot associate %CONTAINSTERM
with another predicate condition using the OR logical operator if the two predicates reference fields in different tables. For example:
WHERE t1.text %CONTAINSTERM('Continental United States') OR t2.Timezone BETWEEN 5 AND 8
Because this restriction depends on how the optimizer uses indices, SQL may only enforce this restriction when indices are added to a table. It is strongly suggested that this type of logic be avoided in all queries.
The following Embedded SQL example performs a %CONTAINSTERM
comparison with a literal phrase:
INTO :badname, :badstat
WHERE status %CONTAINSTERM('invalid value'))
The following Embedded SQL example uses a host variable to perform a %CONTAINSTERM
INTO :badname, :badstat
WHERE status %CONTAINSTERM(:text))