Db2 Text Search argument syntax

A search argument comprises one or more terms and optional search parameters, separated by white space, that you specify to search text documents.

When you specify a term, the search engine returns documents that contain that term and, by default, variations on that term. For example, if you search by using the term king, documents containing king and kings are returned. If you search by using multiple terms, the search engine returns only documents containing all the terms.

If you want to search by using an exact phrase, surround the phrase in quotation marks. Use a fuzzy search to find documents that contain words with spelling similar to that of the search term. A common reason to perform a fuzzy search is to include documents that contain misspellings in the search result.

Perform a proximity search to retrieve documents containing search words that are located within a specified distance from each other.

Remember:

Searches are not case sensitive, so a search in Spanish for the exact term "DOS" might return documents containing DOS or dos.
Text search queries must not exceed Db2 SQL query limits.

The more specific the search term that you use, the more precise the results. However, you can also refine your searches by using options such as the following ones:

Boolean operators

Use the AND operator to search for documents that contain all the specified terms. The AND operator is the default conjunction operator. If there is no logical operator between the two terms, AND is used.

Use the AND operator to search for documents that contain all the specified terms.The OR operator links the two or more terms and finds a matching document if either of the terms exists in a document.

Occurrence modifiers

Use a plus sign (+) to specify that terms are required. The plus sign (+) modifier is distinct from the AND operator because the plus sign (+) modifier indicates that the second term must be an exact match. No synonym is used.

Use a minus sign (-) or the NOT modifier to specify that terms are prohibited.

The boost modifier

Use the caret (^) character to give higher importance to occurrences of a particular term. The caret (^) character provides a boost to the term or phrase that precedes it when the specified number is greater than 1. If you want to reduce the ranking of the term or phrase in the returned list, specify a number that is greater than 0 but less than 1.

Use the boost modifier with the SCORE function or the ORDER BY clause.

Wildcard characters

Use a question mark (?) to specify that a single character can be added to your search term. Use an asterisk (*) to specify that any number of characters can be added to your search term. Use these wildcard characters to search terms and data for spelling variations and increase search scope.

Important: Using the asterisk (*) wildcard at the beginning of a search term negatively affects the performance of the search query.

Wildcard searches with an asterisk (*) apply a term expansion to find documents. If the number of matching terms in the text index collection exceeds the expansion limit, only a subset of documents that match the criteria is returned. See the text search arguments topic for further details. Also, wildcard searches find regular characters, not special characters. For example, searching for US-*-abc finds strings such as US-xxx-abc, US-x-abc, and US-x#-abc but not US-#-abc.

The percentage sign (%)

Use a percentage sign (%) to specify that a term or phrase is optional.

The backslash (\) escape character

Use a backslash (\) to include special characters in your search. All of the following characters are special characters in text search queries:

<
>
&&
||
!
(
)
%
=
"
{
}
~
*
?
[
]
:
\
-

Double quotation marks (")

Use quotation marks (") around your search term or phrase to have only exact matches returned.

Parentheses

Use parentheses to have search terms and the relationship between them treated as a single item.

For XML search queries that are sent to the XML parser, write the queries by using opaque terms in a subset of the XPath language. The query parser recognizes an opaque term by the syntax that you use in the query.

For any language-specific processing during a search, a locale is assumed for the search-argument parameter. This query language is the locale of the text search index that is used when you perform the search function.

The search argument syntax is as follows:

Search argument

QualifiedClause ((Operator) (QualifiedClause))

Operator

AND | OR

QualifiedClause

(Modifier) Clause (^number)

Modifier

+ | - | NOT

Clause

Unqualified term | opaque term

An unqualified term is a term or a phrase. A term can be a word, such as king; an exact word, such as "king"; or a word that includes a wildcard, such as king* or king?. Similarly, a phrase can be a group of words, such as cabbages and kings; an exact phrase, such as "The King and I"; or a phrase that includes a wildcard, such as all the king's h* or all the kin?'s horses.
An opaque query term is not parsed by the linguistic query parser; opaque terms are identified by their syntax. The opaque term that is used for text search queries is @xpath, for example, @xpath:'/TagA/TagB[. contains("king")]'.

Examples

Table 1. Boolean operators
Operator	Example	Query results
AND	`King AND Lear King Lear`	Returns documents that contain the terms `King` and `Lear`. If you enable a synonym dictionary, words such as `monarch` can also be returned.
OR	`Hamlet OR Othello`	Returns documents that contain either `Hamlet` or `Othello`.

Table 2. Occurrence modifiers
Modifier	Example	Query result
NOT -	`Hamlet NOT Othello` `Hamlet -Othello`	Returns documents that contain `Hamlet` but not `Othello`.
+	`Lear + King`	Returns documents that contain the terms `Lear` and `King`. Documents containing `Lear` and `monarch` are not returned.

Table 3. Other modifiers
Modifier	Example	Query result
`term1` or `phrase1`^`number` `term2` or `phrase2`	`Hamlet^2 Othello` `Hamlet Othello^.5`	Returns documents containing `Hamlet` and `Othello` but gives more importance to the term `Hamlet`. In both example queries, each occurrence of the term `Hamlet` is given twice as much importance as each occurrence of `Othello` is given.
*	`king* kng ing`	Returns documents that contain possible combinations of the search term with the wildcard character. The example query might return results such as `king` and `kingdom` in the first example, `king` and `kissing` in the second example, and `king` and `skiing` in the third example.
*	`www.*.com`	Searching using wildcards does not return terms that contain special characters. The example query might return `www.ibm.com` but does not return `www.#.com`.
?	`mea? be?n ?ean`	Returns documents that contain possible combinations of the search term with the wildcard character. The first example returns results such as `meal` and `mean`, the second example returns results such as `bean` and `been`, and the third example returns results such as `mean` and `bean`.
%	`King James %Edition`	Returns documents that contain both `king` and `james`, but `edition` is an optional term.
"`phrase`" "`exact term`" "`phrase with wildcard`"	`"King Lear" "king" "John * Kennedy" "John ? Kennedy"`	Returns documents that contain the exact word or phrase. The first example returns `King Lear`. The second example returns the word `king` and no other forms, such as `kings` or `kingly`. You can use quotation marks with wildcards. The third example returns occurrences of `John Kennedy` with or without various middle names or initials. The fourth example returns `John initial Kennedy`.
( )	`(Hamlet OR Othello) AND plays`	Returns documents that contain the following terms: The term `Hamlet` or `Othello` The term `plays`
\	`\(1\+1\)\:2`	Returns documents that contain (1+1):2. Use the backslash (\) character to escape special characters that are part of the query syntax.