Wildcard searches

You can use wildcards in basic text search queries on single terms. You cannot use wildcards in searches on phrases.

To perform a single-character wildcard search, use a question mark (?) in the search term. The single-character wildcard search looks for terms that match with the single character replaced. For example, to search for the terms text and test, use te?t in the search predicate:

bts_contains(column, 'te?t')

You can use a single wildcard character (?) as the first character of the search term.

Multiple-character wildcard searches

Multiple-character wildcard searches look for zero or more characters.

To perform a multiple-character wildcard search, use an asterisk (*) in the search term. For example, to search for geo, geography, and geology, use geo* in the search predicate:

bts_contains(column, 'geo*')

The multiple-character wildcard search can also be in the middle of a term. For example, the search term c*r will match contour, crater, color, and any other words that start with the letter c and end with the letter r:

bts_contains(column, 'c*r')

You cannot use a multiple wildcard character (*) as the first character of the search term.

If the number of indexed tokens that match your wildcard query exceed 1024, you receive the following error:

(BTSB0) - bts clucene error: Too Many Clauses

To solve this problem, you can make the query more restrictive or you can recreate the bts index with the max_clause_count index parameter set to a number greater than 1024.