Basic Text Search query syntax

You can also run a basic text search query with the HCL OneDB™ JSON $ifxtext query operator. Use the same syntax for the search criteria for both methods.

column

The column to be searched. It must be a single column for which a bts index is defined.

query_string

The search string. The search string includes the following elements:

Query term

Required. One or more words that you want to search for.

Query term modifiers

Optional. You can modify query terms to run wildcard, fuzzy, proximity, and range searches. You can boost the importance of a query term relative to other terms.

Boolean operators

Optional. You can include Boolean operators to combine query terms in logical combinations.

If an index has multiple fields because it is a structured or a composite index, you can include an index field name to modify the search string.

score # REAL

Optional argument that is used to pass a statement local variable (SLV) to the text search engine. The search engine uses this variable to record the document score it assigns to each row in the results. The score value is a REAL number between 0.0 and 100.0 inclusive that indicates the relevance of each document to the search criteria, compared to that of other indexed records. The higher the document score value, the more closely the document matches the criteria.

SELECT id FROM products 
WHERE bts_contains(brands, 'standard');

You can use an SLV as a filtering mechanism and to sort the results by score. The following example returns documents that contain the word standard from the column brands in a table that is called products if the document score value is greater than 70. The results are ordered in descending order by score.

SELECT id FROM products 
WHERE bts_contains(brands, 'standard', score # REAL)
AND score > 70.0;
ORDER BY score DESC;